<?
/**
 * Debug functions
 * Returns as print_r(arg) 
 */
function p(){
	$args = func_get_args();
	foreach ($args as $arg){
		echo '<pre>';
		print_r($arg);
		echo '</pre>'."\n";
	}
}
function pe(){
	$args = func_get_args();
	foreach($args as $arg){
		echo '<pre>';
		print_r($arg);
		echo '</pre>'."\n";
	}
	exit;
}

/**
 * Returns data as JSON string
 * @param $var
 */
function json_enc($var){
	switch (gettype($var)){
		case 'boolean':
			return $var ? 'true' : 'false'; // Lowercase necessary!
		case 'integer' :
		case 'double' :
			return $var;
		case 'resource' :
		case 'string' :
			return '"' . str_replace(array("\r", "\n", "<", ">", "&"), array('\r', '\n', '\x3c', '\x3e', '\x26'), addslashes($var)) . '"';
		case 'array' :
			if(array_keys($var) === range(0, sizeof($var) - 1)){
				$output = array();
				foreach($var as $v){
					$output[] = json_enc($v);
				}
				return '[ '.implode(', ',$output ).' ]';
			}
		case 'object' :
			$output = array();
			foreach($var as $k => $v){
				$output[] = json_enc(strval($k)).': '.json_enc($v);
			}
			return '{ '.implode(', ', $output).' }';
		default :
			return 'null';
	}
}

/**
 * * Substitute get parameter
 * @example $url = '/article.php?view=flat&page=3';
 * 			sgp($url, 'page', 4); // -->  '/article.php?view=flat&page=4'
 * 			sgp($url, 'view', 'tree'); // -->  '/article.php?view=tree&page=3'
 *			sgp($url, 'view', ''); // --> '/article.php&page=3'
 */
function sgp($url, $varname, $value){
	preg_match('/^(.+?)(\?.*?)?(#.*)?$/', $url, $matches);
	$gp = (isset($matches[2])) ? $matches[2] : ''; // GET-parameters
	$pattern = "/([?&])$varname=.*?(?=&|#|\z)/";

	if (preg_match($pattern, $gp)){
		$substitution = ($value !== '') ? "\${1}$varname=$value" : '';
		$newgp = preg_replace($pattern, $substitution, $gp); // new GET-parameters
	}
	else{
		$s = ($gp) ? '&' : '?';
		$newgp = $gp.$s.$varname.'='.$value;
	}

	$anchor = (isset($matches[3])) ? $matches[3] : '';
	$newurl = $matches[1].$newgp.$anchor;
	return $newurl;
}


/**
 * Returns list of categories
 */
function getCategories(){
	global $db;

	$sql = "SELECT categoryID,categoryTitle FROM " . T_CATS . " ORDER BY categoryTitle";
	$db->query($sql);
	$categories = array();
	while($db->next_record()){
		$categories[$db->f("categoryID")] = $db->f("categoryTitle");
	}
	return $categories;
}

/**
 * Return list of countries
 */
function getCountries(){
	global $db;

	$sql = "SELECT * FROM " . T_COUNTRIES . " ORDER BY countryTitle";
	$db->query($sql);
	$countries = array();
	while($db->next_record()){
		$countries[$db->f("countryID")] = array('countryCurrencyChar'=>$db->f("countryCurrencyChar"), 'countryTitle' => $db->f("countryTitle"));
	}
	return $countries;
}

/**
 * Returns array of purchase statuses
 */
function getStatuses(){
	global $db;

	$sql = "SELECT * FROM " . T_PSTATUSES . " ";
	$db->query($sql);
	$statuses = array();
	while($db->next_record()){
		$statuses[$db->f("statusID" )] = $db->f("statusTitle");
	}
	return $statuses;
}

/**
 * Returns array of types of payments
 */
function getPaymentTypes(){
	global $db;

	$sql = "SELECT * FROM " . T_PTYPES . " ";
	$db->query($sql);
	$types = array();
	while($db->next_record()){
		$types[$db->f("ptypeID" )] = $db->f("ptypeTitle");
	}
	return $types;
}

/**
 * Fixes the problem of double converted html entities
 * @return 
 * 		updated string
 */
function removeHTMLCrap($s){
	$s = preg_replace("@(amp;)+([#u]{0,1}\w+)@", "$2", $s );
	return str_replace(array('&amp;', '&quot;', '&#039;', '&lt;', '&gt;', '&#x28;', '&#x29;' ), array('', '', "", '', '', '', '' ), $s);

}

/**
 * Removing "bad" html code, coverts it to html entities
 * killing possible XSS attack
 * @return 
 * 		updated string
 */
function hsc($s){
	$s = str_replace(array("(", ")", "\\", "'" ), array("&#x28;", "&#x29;", "\\\\", "&#039;" ), $s);
	$s = htmlspecialchars(trim( $s ), ENT_QUOTES );
	$s = preg_replace ( "@(amp;)+([#u]{0,1}\w+;)@i", "$2", $s );
	return $s;
}

/**
 * Synonym for unhtmlspecialchars
 * @return 
 * 		updated string
 */
function uhsc($s){
	$s = str_replace ( array("&#x28;", "&#x29;", "\\\\", "&#039;" ), array("(", ")", "\\", "'" ), $s );
	return unhtmlspecialchars ( $s );
}

/**
 * Makes reversal conversion of html entities to html
 * @return 
 * 		updated string
 */
function unhtmlspecialchars($string){
	$string = preg_replace ( "@(amp;)+([#u]{0,1}\w+)@i", "$2", $string );
	$string = str_replace ( '&amp;', '&', $string );
	$string = str_replace ( '&#039;', "'", $string );
	$string = str_replace ( '&quot;', '"', $string );
	$string = str_replace ( '&lt;', '<', $string );
	$string = str_replace ( '&gt;', '>', $string );
	return $string;
}

function errorMessage($title, $url = "", $details = ""){
	global $smarty;

	$smarty->assign ( "title_page", $title );
	$smarty->assign ( "url", $url );
	$smarty->assign ( "details", $details );

	$smarty->display ( "error.html" );
	die();
}

/**
 * Generate 404 error
 */
function errorNotFound( ){
	global $smarty;
	$smarty->display('404.html');
	exit();
}

/**
 * Fixing events to db
 */
function logEventDB($userID = 0, $userEmail, $ip=0, $message, $value=0, $data, $type){
	global $db;
	if(is_object($data) || is_array($data)) $data = serialize($data);
	if(!$ip) $ip = $_SERVER['REMOTE_ADDR'];
	$referer = hsc($_SERVER['HTTP_REFERER']);
	$uri = hsc($_SERVER['REQUEST_URI']);
	$GMTnow = getGmtDBDate ();
	$sql = "INSERT INTO " . T_WATCHLOG . " SET userID='$userID', userEmail='$userEmail', ip='$ip', message='$message', value='$value', uri='$uri', referer='$referer',timestamp='$GMTnow',data='$data',type='$type'";
	$db->query($sql);
}

/**
 * Log system event into database *
 *
 * @param int $partnerID
 * @param int $personID
 * @param string $personType ('g'-guest, 'u'-user, 'p'-partner, 'a'-admin)
 * @param string $type
 * @param string $message
 * @param string $extra
 */
function logDB($partnerID=0,$personID=0,$personType='',$type='',$message='',$extra=''){
	global $db;
	
	$message = hsc($message);
	$extra = hsc($extra);
	$logIP = $_SERVER['REMOTE_ADDR'];
	$logURI = hsc($_SERVER['REQUEST_URI']);
	
	$sql = "INSERT INTO ".T_LOGS." SET 
				partnerID='$partnerID', logPersonID='$personID', logPersonType='$personType', logType='$type', logMessage='$message', 
				logExtra='$extra', logAdded=NOW(), logIP = '$logIP', logURI='$logURI'";
		
	$db->query($sql);
}



function array_walk_recursive2(&$input, $funcname, $userdata = ""){
	if (! is_callable ( $funcname )){
		return false;
	}

	if (! is_array( $input )){
		return false;
	}

	foreach ( $input as $key => $value ){
		if (is_array( $input [$key] )){
			array_walk_recursive2 ( $input [$key], $funcname, $userdata );
		} else {
			$saved_value = $value;
			if (! empty ( $userdata )){
				$funcname ( $value, $userdata );
			} else {
				$value = $funcname ( $value );
			}
			if ($value != $saved_value){
				$input [$key] = $value;
			}
		}
	}
	return true;
}

/**
 * @param the csv line to be split
 * @param the delimiter to split by (default ';' )
 * @param if this is false, the quotation marks won't be removed from the fields (default true)
 */
function mb_csv_split($line, $delim = ';', $removeQuotes = true){
	$fields = array();
	$fldCount = 0;
	$inQuotes = false;

	for($i = 0; $i < mb_strlen ( $line ); $i ++){
		if (! isset ( $fields [$fldCount] ))
		$fields [$fldCount] = "";
		$tmp = mb_substr ( $line, $i, mb_strlen ( $delim ) );
		if ($tmp === $delim && ! $inQuotes){
			$fldCount ++;
			$i += mb_strlen ( $delim ) - 1;
		} else if ($fields [$fldCount] == "" && mb_substr ( $line, $i, 1 ) == '"' && ! $inQuotes){
			if (! $removeQuotes)
			$fields [$fldCount] .= mb_substr ( $line, $i, 1 );
			$inQuotes = true;
		} else if (mb_substr ( $line, $i, 1 ) == '"'){
			if (mb_substr ( $line, $i + 1, 1 ) == '"'){
				$i ++;
				$fields [$fldCount] .= mb_substr ( $line, $i, 1 );
			} else {
				if (! $removeQuotes)
				$fields [$fldCount] .= mb_substr ( $line, $i, 1 );
				$inQuotes = false;
			}
		} else {
			$fields [$fldCount] .= mb_substr ( $line, $i, 1 );
		}
	}
	return $fields;
}
/**
 * Returns unique long key
 *
 */
function uid(){
	return md5(uniqid(rand(),true));
}
/**
 * Returns unique short key (6 symbols)
 *
 */
function generatePassword(){
	return substr(md5(uniqid(rand(), true)), 0, 6);
}
/**
 * Execute another function with special parameters and exists checking 
 *
 * @param string $funcName
 * @param var $inputParameters
 * @param var $outputParameters
 */
function callTrigger($funcName, &$inputParameters, &$outputParameters){
	if(function_exists($funcName)){
		eval('$outputParameters='.$funcName.'($inputParameters);');
		return true;
	}else{
		return false;
	}
}

/**
 * Send out HTML formatted email
 *
 * @param string $to
 * @param string $subject - email body
 * @param string $html - email body
 * @param string $from - the sender's email address
 * @param string $fromName - the name of sended
 * @return bool
 */
function mailHtml($to, $subject, $html, $from = '', $fromName=''){
	// To send HTML mail, the Content-type header must be set
	$headers  = 'MIME-Version: 1.0' . "\n";
	$headers .= 'Content-type: text/html; charset=utf-8' . "\n";

	// Additional headers
	//$headers .= 'To: '.$to. "\n";
	$headers .= 'From: '.$fromName.' <'.$from.'>' . "\n";

	// Mail it
	return mail($to, $subject, $html, $headers);
}

//***************************************************************************************//
//********************************* SITE RELATED FUNCTIONS ******************************//
//***************************************************************************************//
/**
 * Returns array of site details
 *
 * @param int $partnerID
 */
function getSiteInfo($partnerID=0){
	global $db;
	if($partnerID){
		$db->query("SELECT p.*, c.countryCurrencyChar partnerCurrencyChar
					FROM ".T_PARTNERS." p
					LEFT OUTER JOIN ".T_COUNTRIES." c ON(c.countryID = p.countryID) 	
					WHERE partnerID='{$partnerID}' LIMIT 1");
		$db->next_record();
		return $db->Record;
	}
	return 0;
}

/**
 * Returns the number of users
 *
 * @param int $partnerID
 * @return int
 */
function getCountOfUsers($partnerID=0){
	global $db;
	$sql = "SELECT count(userID) AS cnt FROM ".T_USERS." WHERE 1=1 ".($partnerID?" AND partnerID = '{$partnerID}'":'');
	$db->query($sql);
	$db->next_record();
	return $db->f('cnt');
}
//***************************************************************************************//
//***************************** CATEGORIES RELATED FUNCTIONS ****************************//
//***************************************************************************************//
function loadCategoriesChain($categoryID){
	$output = array();
	if (getCategory($categoryID)){
		getCategoryRecursiveToParent($output, $categoryID);
		$output [] = array("categoryParentID" => "", "categoryID" => "0", "categoryTitle" => "Root" );
	}
	$output = array_reverse($output);
	return $output;
}
/**
 * Returns category details by categoryID
 * 
 * @param int $categoryID
 */
function getCategory($categoryID){
	global $db;
	$sql = "SELECT * FROM " . T_CATS . " WHERE categoryID='{$categoryID}'";
	$db->query($sql);
	if ($db->next_record()){
		$output = $db->Record;
	}
	return $output;
}
/**
 * Returns count of subcategories for current category
 *
 * @param int $categoryID
 * @return count
 */
function getSubCategoryCount($categoryID){
	global $db;
	$sql = "SELECT count(*) as cnt FROM " . T_CATS . " WHERE categoryID='{$categoryID}'";
	$db->query($sql);
	if ($db->next_record()){
		$output = $db->f('cnt' );
	}
	return $output;
}

/**
 * recursive function to retirieve full categories tree
 */
function getCategoryRecursiveToParent(&$output, $categoryID){
	if ($categoryID == "0")
	return;
	$item = getCategory ( $categoryID );
	$output [] = $item;
	getCategoryRecursiveToParent ( $output, $item ["categoryParentID"] );
}

/**
 * Returns all the childs in the tree-like way
 */
function loadCategoriesTree($categoryID, $limits = array()){
	$output [$categoryID] = getCategory ( $categoryID );
	getCategoryRecursiveToChildren ( $output [$categoryID] ['children'], $categoryID, $list, $limits );
	return $output;
}

/**
 * Returns all the childs in the plain array
 */
function loadCategoriesList($categoryID){
	$output[$categoryID] = getCategory($categoryID);
	$list[] = $categoryID;
	getCategoryRecursiveToChildren ( $output [$categoryID] ['children'], $categoryID, $list );
	return $list;
}

/**
 * Returns array with complete tree of children
 */
function getCategoryRecursiveToChildren(&$tree, $categoryID, &$list, $limits = array()){
	global $db;
	$sql = "SELECT * FROM " . T_CATS . " WHERE categoryParentID='{$categoryID}' ORDER BY categoryTitle ASC";
	$db->query($sql);

	$items = array();
	while ( $db->next_record() ){
		$items [$db->f("categoryID" )] = $db->Record;
	}
	if (is_array( $items ) && count ( $items )){
		foreach ( $items as $k => $v ){
			if (count ( $limits ) && ! in_array( $k, $limits ))
			continue;
			getCategoryRecursiveToChildren ( $items [$k] ['children'], $k, $list );
			$list [] = $k;
		}
		$tree = $items;
	}

}

/**
 * Returns list of categories
 */
function loadCategories($activeCategoryID = 0){
	global $db;

	$output = $categories = array();
	if (getCategory ( $activeCategoryID ) || $activeCategoryID == 0){

		getCategoryRecursiveToParent ( $output, $activeCategoryID );
		$output [] = array("categoryParentID" => "", "categoryID" => "0", "categoryTitle" => "Root" );
		$output = array_reverse ( $output );

		$limits = array();
		foreach ( $output as $k => $v ){
			$limits [] = $v ['categoryID'];
		}

		$categories = loadCategoriesTree ( 0, $limits );
	}
	return $categories;
}

/**
 * Returns category title by categoryID
 *
 * @param int $categoryID
 */
function getCategoryName($categoryID){
	global $db;

	$sql = "SELECT categoryTitle FROM " . T_CATS . " WHERE categoryID='$categoryID'";
	$db->query($sql);
	while ( $db->next_record() ){
		return $db->f("categoryTitle" );
	}
}

/**
 * Return categoryID by category title
 *
 * @param $categoryTitle
 */
function getCategoryByName($categoryTitle){
	global $db;

	$sql = "SELECT categoryID FROM " . T_CATS . " WHERE LOWER(categoryTitle)=LOWER('$categoryTitle')";
	$db->query($sql);

	//trying to match in the part
	if (! $db->next_record()){
		$sql = "SELECT categoryID FROM " . T_CATS . " WHERE categoryTitle like '%$categoryTitle%'";
		$db->query($sql);
		$db->next_record();
	}

	return $db->f("categoryID" );
}
/**
 * Returns categoryID basing on URL otherwise returns 0
 * 
 */
function getCategoryByURL($categoryURL){
	global $db;

	$sql = "SELECT categoryID,categoryTitle FROM " . T_CATS . "";
	$db->query($sql);

	$categoryID = 0;

	//trying to match in the part
	while ( $db->next_record() ){
		if (makeURL ( $db->f('categoryTitle' ) ) == $categoryURL){
			$categoryID = $db->f('categoryID' );
			break;
		}
	}
	return $categoryID;
}

/**
 * Marks and unmarks category as «visible» for current partner
 *
 * @param int $categoryID
 * @param int $partnerID
 * @param bool $enable
 */
function setCategoryPartner($categoryID, $partnerID, $enable=1){
	global $db;
	$db->query("DELETE FROM ".T_CATEGORIES_PARTNERS." WHERE categoryID={$categoryID} AND partnerID={$partnerID}");
	if($enable){
		$db->query("INSERT INTO ".T_CATEGORIES_PARTNERS." SET categoryID={$categoryID}, partnerID={$partnerID}");
	}
}


/**
 * Creates a SEO-friendly URL basing on a string
 */
function makeURL($s){
	$s = strtolower ( str_replace ( "&amp;", "and", $s ) );
	return (preg_replace ( "@[^\w/\-]@", '-', $s ));
}

function redirect($url){
	header( "Location: {$url}" );
	die();
}
function fromDBDate($date){
	return preg_replace("@(\d+)-(\d+)-(\d+).*@", "$2/$3/$1", $date);
}
function toDBDate($date){
	return preg_replace("@(\d+)/(\d+)/(\d+)@", "$3-$1-$2", $date);
}
function getDBDate($date = 0){
	if (!$date)
	$date = time();
	return date("Y-m-d h:i:s", $date);
}
function getGmtDBDate(){
	return date ( "Y-m-d H:i:s", time() - date ('Z', time()));
}
/**
 * Write actions to log file 
 */
function logEvent($body = ""){
	$fp = fopen ( ROOT . "./log/logs.txt", "a+" );
	$body = str_replace ( "\r", "", $body );
	$body = str_replace ( "\n", "", $body );
	//$body=str_replace("\t"," ",$body);
	//$body=str_replace("  "," ",$body);
	fputs ( $fp, date ( "m-d-Y H:i:s" ) . "\t" . $_SERVER ['REMOTE_ADDR'] . "\t" . $body . "\t" . $_SERVER ["REQUEST_URI"] . "\n" );
	fclose ( $fp );
}

function microtime_float(){
	list ( $usec, $sec ) = explode (" ",microtime());
	return (( float ) $usec + (float)$sec);
}

/**
 * Checks the image to fit allowed extensions and optionally resize it
 */
function checkImage($image, $newName = "", $width = 0, $height = 0){
	GLOBAL $allowedUploadExt;
	if (! is_uploaded_file ( $image ['tmp_name'] ))
	return false;
	//if(filesize($image['tmp_name'])>MAX_FILE_SIZE) return false;


	$fe = explode ( ".", $image ['name'] );
	if (in_array(strtolower($fe[sizeof($fe)-1]), $allowedUploadExt)){
		//resizing image
		if (($width || $height) && $newName){
			resizeImage ($image ['tmp_name'], $newName, $width, $height);
		}
		return true;
	}else{
		return false;
	}
}

/**
 * resize the image accordingly to dimensions provided
 */
function resizeImage($inName, $outName, $width, $height){
	if ($in = imagecreatefromjpeg ( $inName ))
	; elseif ($in = imagecreatefrompng ( $inName ))
	; elseif ($in = imagecreatefromgif ( $inName ))
	; else {
		//image wasn't resized...
		copy ( $inName, $outName );
		return false;
	}
	//making our resize
	$sx = imagesx ( $in );
	$sy = imagesy ( $in );

	if (! ($sx && $sy))
	return false;
	$z = $sy / $sx;
	if ($sx <= $width && ($sy <= $height || ! $height)){
		copy ( $inName, $outName );
		return false;
	} elseif ($sx > $width && $sy > $height && $height){
		if ($sx > $sy){
			$wx = $width;
			$wy = $z * $wx;
		} else {
			$wy = $height;
			$wx = $wy / $z;
		}
	} elseif ($sy > $height && $height){
		$wy = $height;
		$wx = $wy / $z;
	} else {
		$wx = $width;
		$wy = $z * $wx;
	}

	$out = @imagecreatetruecolor ( $wx, $wy );
	imagecopyresampled ( $out, $in, 0, 0, 0, 0, $wx, $wy, $sx, $sy );
	imagedestroy ( $im );

	imagejpeg ( $out, $outName );
	return true;
}

//*********************************************** ADMINS ORIENTED FUNCTIONS *************************************************

/**
 * Returns list of master admins
 *
 * @return array
 */
function getAdmins(){
	global $db;

	$sql = "SELECT adminID, adminName FROM " . T_ADMIN . " ";
	$db->query($sql);
	$admins = array();
	while ( $db->next_record() ){
		$admins [$db->f("adminID" )] = $db->f("adminName" );
	}
	return $admins;
}


//************************************************* PURCHASE ORIENTED FUNCTIONS ***********************************************

/**
 * Returns treshold value of partner default group
 *
 * @param  int $partnerID
 */
function getDefaultThreshold($partnerID=0){
	if(is_numeric($partnerID)){
		global $db;
		$sql = "SELECT groupThreshold FROM " . T_GROUPS . " WHERE groupDefault='y' AND partnerID='$partnerID'";
		$db->query($sql);
		$db->next_record();
		return $db->f('groupThreshold');
	}else{
		return 0;
	}
}
/**
 * Returns groupID of partner default group
 *
 * @param int $partnerID
 * @return int groupID
 */
function getDefaultGroup($partnerID=0){
	if(is_numeric($partnerID)){
		global $db;
		$sql = "SELECT groupID FROM " . T_GROUPS . " WHERE groupDefault='y' AND partnerID='$partnerID'";
		$db->query($sql);
		$db->next_record();
		return $db->f('groupID');
	}else{
		return 0;
	}
}

/**
 * Returns groupID of partner default group
 *
 * @param int $partnerID
 * @return int groupID
 */
function loadPartnerGroups($partnerID=0){
		global $db;
		$sql = "SELECT groupID,groupTitle FROM " . T_GROUPS . " WHERE 1=1 ".($partnerID?" AND partnerID='$partnerID'":'')." ORDER BY groupDefault DESC";
		$db->query($sql);
		$groups=array();
		while($db->next_record()){
			$groups[$db->f('groupID')] = $db->f('groupTitle');
		}
		return $groups;
}

/**
* Returns details of purchase by purchaseID	
 *
 * @param int $purchaseID
 * @return purchase as array
 */
function loadPurchaseDetails($purchaseID){
	global $db;
	$sql = "
		SELECT pm.merchantRebate,
			   c.partnerCommission,
			   m.merchantTitle,
			   m.merchantCommission,
			   m.merchantCommissionFixed,
			   a.partnerTitle,
			   a.partnerEmail,
			   u.userEmail,
			   u.userID,
			   p.*,
			   u.userName,
			   u.userSurname,
			   p.purchaseUserCommission dueUser,
			   p.purchasePartnerCommission duePartner
		FROM " . T_PURCHASES . " p
			LEFT JOIN " . T_PARTNERS_COMMISSIONS . " c ON (p.partnerID = c.partnerID) AND (p.merchantID = c.merchantID)
		  LEFT JOIN ". T_CLICKS ." cl ON (p.clickID = cl.clickID)
			LEFT JOIN " . T_PARTNERS_MERCHANTS . " pm ON (c.merchantID = pm.merchantID) AND (c.partnerID = pm.partnerID)
			LEFT JOIN " . T_PARTNERS . " a ON (pm.partnerID = a.partnerID)
			LEFT JOIN " . T_MERCHANTS . " m ON (c.merchantID = m.merchantID)
			LEFT JOIN " . T_USERS . " u ON (p.userID = u.userID)
		WHERE 
			p.purchaseID='$purchaseID'";

	$db->query($sql);
	$db->next_record();
	$purchase = $db->Record;

	return $purchase;
}

/**
* Returns list of invoices
 * @return array
 */
function loadInvoices($partnerID, $exchangeRate = 0){
	global $db;
	$partner = new dbObject($db,T_PARTNERS,$partnerID);
	if($partner->profile['partnerUseDestiantions']==1){
		$filter = " AND invoiceAmount * m >=g.groupThreshold"; // m - min destianation rebate for current user
	}else{
		$filter = " AND invoiceAmount>=g.groupThreshold";
	}

	$partnerPreviousDate = getPreviousPartnerDate($partnerID);
	if($exchangeRate){
		$sql = "SELECT i.*,u.*,invoiceAmountPoints as invoicePoints FROM " . T_INVOICES . " i
			INNER JOIN " . T_USERS . " u ON (i.userID=u.userID AND i.partnerID=u.partnerID)
			INNER JOIN " . T_GROUPS . " g ON (u.groupID = g.groupID)
			LEFT JOIN (SELECT userID, MIN(udRebate) m FROM ".T_USERS_DESTINATIONS." WHERE udDisabled=0 GROUP BY userID) ud 
						ON (u.userID = ud.userID)
			WHERE i.partnerID='$partnerID' AND (i.invoiceDate > '{$partnerPreviousDate}' OR i.invoicePaid='n') ".$filter; 
	}else{
		$sql = "SELECT i.*,u.* FROM " . T_INVOICES . " i
			INNER JOIN " . T_USERS . " u ON (i.userID=u.userID AND i.partnerID=u.partnerID)
			INNER JOIN " . T_GROUPS . " g ON (u.groupID = g.groupID)
			LEFT JOIN (SELECT userID, MIN(udRebate) m FROM ".T_USERS_DESTINATIONS." WHERE udDisabled=0 GROUP BY userID) ud 
						ON (u.userID = ud.userID)
			WHERE i.partnerID='$partnerID' AND (i.invoiceDate > '{$partnerPreviousDate}' OR i.invoicePaid='n') ".$filter;
	}

	$db->query($sql);
	$invoices = $db->getArray();
	if($partner->profile['partnerUseDestiantions']==1){
		foreach($invoices as $k=>$invoice){
			$sql = "SELECT ud.destinationID, d.*, ud.udRebate, (ud.udRebate * i.invoiceAmount) amount
							FROM ".T_USERS_DESTINATIONS." ud
							LEFT JOIN ".T_DESTINATIONS." d ON (d.destinationID = ud.destinationID)
							LEFT JOIN ".T_INVOICES." i ON (ud.userID = i.userID)
						WHERE invoiceID = '{$invoice['invoiceID']}'";
			$db->query($sql);

			$invoices[$k]['destinations'] = $db->getArray();
			$invoices[$k]['destinationRebates'] = 0;
			foreach ($invoices[$k]['destinations'] as $i=>$d){
				$invoices[$k]['destinationRebates']+=$d['udRebate'];
				$invoices[$k]['destinations'][$i]['destinationData'] = unserialize($d['destinationData']);
			}

			if(round($invoices[$k]['destinationRebates'],4)!=1){// user didn't distribute 100% between destinations
				unset($invoices[$k]);
			}

		}
	}
	return $invoices;
}


/**
 * Set status invoice
 *
 * @param int $partnerID
 * @param int $invoiceID
 * @param bool $checked
 */
function updateInvoiceStatus($partnerID, $invoiceID, $checked){
	global $db;

	if($checked){
		$invoice = new dbObject($db, T_INVOICES, $invoiceID);
		$db->query("SELECT purchaseID FROM " . T_PURCHASES . " WHERE userID='{$invoice->profile['userID']}' AND statusID = 2");
		$purchasesIDs = array(0);
		foreach ($db->getArray() as $k=>$v){
			$purchasesIDs[] = $v['purchaseID'];
		}
		$purchasesIDs = implode(',',$purchasesIDs);

		//set purchases' status to "Paid"
		$db->query("UPDATE ".T_PURCHASES." SET statusID = 5 WHERE purchaseID IN ({$purchasesIDs})");

		// update invoice
		$db->query("SELECT SUM(purchaseUserCommission) summ,SUM(purchaseUserCommissionPoints) summPoints 
						FROM " . T_PURCHASES . " WHERE purchaseID IN ({$purchasesIDs})");
		$db->next_record();
		$invoice->profile['invoiceAmount'] = $db->f('summ');
		$invoice->profile['invoiceAmountPoints'] = $db->f('summPoints');
		$invoice->profile['invoicePaid']= 'y';
		$invoice->profile['invoiceUpdated']= getDBDate();
////////		$invoice->saveData();

		$partner = new dbObject($db,T_PARTNERS,$partnerID);
		if($partner->profile['partnerUseDestiantions']==1){
			$sql = "SELECT ud.*, destinationTitle, destinationAccountNumber,
							(ud.udRebate * i.invoiceAmount) amount,(ud.udRebate*i.invoiceAmountPoints) amountPoints
							FROM ".T_USERS_DESTINATIONS." ud
							INNER JOIN ".T_DESTINATIONS." d ON (d.destinationID = ud.destinationID)
							INNER JOIN ".T_INVOICES." i ON (ud.userID = i.userID)
							INNER JOIN ".T_PARTNERS." p ON (p.partnerID = i.partnerID)
						WHERE invoiceID = '{$invoiceID}' AND ud.udDisabled=0 ORDER BY d.destinationType DESC";
			$db->query($sql);

			$list = $db->getArray();
		
			$payments = array(); $rest = 0;
			foreach($list as $item){
				$payment = new dbObject($db, T_PAYMENTS);
				$payment->profile['invoiceID'] = $invoiceID;
				$payment->profile['userID'] = $item['userID'];
				$payment->profile['paymentAmount'] = $item['amount'];
				
				$v 	= $item['amountPoints'];
				$payment->profile['paymentAmountPoints'] = floor($item['amountPoints']);
				$rest += ($v-$payment->profile['paymentAmountPoints']);
				$payment->profile['paymentAccountName'] = $item['destinationTitle'];
				$payment->profile['paymentAccountNumber'] = $item['destinationAccountNumber'];
				$payment->profile['paymentDate'] = getDBDate();
				$payment->saveData();
				$payments[] = $payment;
			}
			$payments[0]->profile['paymentAmountPoints'] += round($rest);
			$payments[0]->saveData();
			
		}else{
			$user = new dbObject($db, T_USERS, $invoice->profile['userID']);
			$payment = new dbObject($db, T_PAYMENTS);
			$payment->profile['invoiceID'] = $invoiceID;
			$payment->profile['userID'] = $invoice->profile['userID'];
			$payment->profile['paymentAmount'] = $invoice->profile['invoiceAmount'];
			$payment->profile['paymentAmountPoins'] = $invoice->profile['invoiceAmountPoints'];
			$payment->profile['paymentDate'] = getDBDate();
			$payment->profile['paymentData'] = serialize($user->profile);
			$payment->saveData();
		}
	}
	return 1;
}

/**
 * Recalculate invoices for user/users
 *
 * @param int $userID
 */
function recalculateInvoices($userID = 0){
	global $db;
	$user = new dbObject($db, T_USERS, $userID);

	$InvoiceNotPaid = new dbObject($db, T_INVOICES,'', " AND userID='{$userID}' AND invoicePaid='n' ORDER BY invoiceDate DESC");
	$sql = "SELECT
				SUM(pr.purchaseUserCommission) summ,
				SUM(pr.purchaseUserCommissionPoints/pr.purchaseExchangeRate) summPoints
			FROM " . T_PURCHASES . " pr
				INNER JOIN ".T_PARTNERS." p ON (p.partnerID = pr.partnerID)
			WHERE pr.userID='{$userID}' AND pr.statusID = 2";
	$db->query($sql);
	$db->next_record();

	if($db->Record['summ'] > 0 && $user->profile['userID']){
		$InvoiceNotPaid->profile['invoiceDate'] = getDBDate();
		$InvoiceNotPaid->profile['invoiceAmount'] = $db->Record['summ'];
		$InvoiceNotPaid->profile['invoiceAmountPoints'] = $db->Record['summPoints'];
		$InvoiceNotPaid->profile['userID'] = $userID;
		$InvoiceNotPaid->profile['partnerID'] = $user->profile['partnerID'];
		$InvoiceNotPaid->saveData();
	}else{
		$InvoiceNotPaid->remove();
	}
}

/**
 * Recalculate balance for user/users
 *
 * @param int $userID

function recalculateUserBalance($userID = 0){
	global $db;
	$sql = "
		REPLACE INTO " . T_INVOICES . "(invoiceID, userID, partnerID, invoiceDate, invoiceAmount, invoicePaid)
		SELECT (SELECT i2.invoiceID FROM " . T_INVOICES . " i2 where i2.invoicePaid='n' and i2.userID=p.userID limit 1) invoiceID,p.userID,p.partnerID,NOW(),SUM(purchaseUserCommission),'n' 
		FROM " . T_PURCHASES . " p
			INNER JOIN " . T_MERCHANTS . " m ON p.merchantID=m.merchantID
		WHERE 
			p.statusID=2 AND
			p.purchaseDate > IFNULL((SELECT MAX(i.invoiceDate) FROM " . T_INVOICES . " i WHERE i.invoicePaid='y' AND i.userID=p.userID),'1981-06-11')
			" . ($userID ? " AND p.userID='$userID'" : "") . "
		GROUP BY p.userID";
	$db->query($sql);
}
 */
/**
* Returns list of invoices
 *
 * @param int $partnerID
 * @param int $statusID
 * @return array
 */
function loadInvoicesList($search, $page, $ppg = PPG){
	global $db;

	$sql = "SELECT i.*,u.*,p.* FROM " . T_INVOICES . " i
			LEFT OUTER JOIN ".T_USERS." u ON (u.userID = i.userID)
			INNER JOIN " . T_PARTNERS . " p ON (p.partnerID = i.partnerID)
		  	WHERE 1=1 $search ";

	$sql_cnt = "SELECT i.*,p.* FROM " . T_INVOICES . " i
				LEFT OUTER JOIN ".T_USERS." u ON (u.userID = i.userID)
				INNER JOIN " . T_PARTNERS . " p ON (p.partnerID = i.partnerID)
		  		WHERE 1=1 $search ";

	$invoices = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
	$invoices->loadData();

	return $invoices;
}

/**
 * Returns invoice details
 *
 * @param int $invoiceID
 * @return array
 */
function loadinvoiceDetails($invoiceID){
	global $db;

	$sql = "SELECT i.*,p.*,u.* FROM " . T_INVOICES . " i
			LEFT OUTER JOIN ".T_USERS." u ON (u.userID = i.userID)
			LEFT OUTER JOIN ".T_PARTNERS." p ON (p.partnerID = i.partnerID)
		  	WHERE invoiceID = '{$invoiceID}'";
	$db->query($sql);
	$db->next_record();
	return $db->Record;
}


/**
* Returns list of actual dates for purchases
 *
 * @param int $partnerID
 * @param int $statusID
 * @return array
 */
function loadPurchaseDates($partnerID, $statusID = ''){
	global $db;

	$sql = "SELECT YEAR(purchaseDate) as year FROM " . T_PURCHASES . " WHERE partnerID='$partnerID' " . ($statusID ? " AND statusID='$statusID' " : '') . "GROUP BY year";
	$db->query($sql);
	$rows = $db->getarray();
	$dates = array();

	foreach ( $rows as $k => $v ){
		$sql = "SELECT LPAD(MONTH(purchaseDate),2,'0') as m, MONTHNAME(purchaseDate) as month,YEAR(purchaseDate) as year  FROM " . T_PURCHASES . " WHERE partnerID='$partnerID' " . ($statusID ? " AND statusID='$statusID' " : '') . "GROUP BY year, month HAVING year='{$v['year']}' ORDER BY year DESC,m ASC";
		$db->query($sql);
		while ( $db->next_record() ){
			$dates [$v ['year']] [$db->f("m" )] = $db->f("month" );
		}
	}
	return $dates;
}
/**
 * Returns stats of purchases
 *
 * @param int $partnerID
 * @param $date
 * @return stats
 */
function getPurchasesStats($partnerID, $date, $purchaseDateField = 'purchaseDate'){
	global $db;
	if($purchaseDateField!='purchaseDate' && $purchaseDateField!='purchaseApprovalDate') $purchaseDateField = 'purchaseDate';
	
	$sql = "SELECT statusID, COUNT(purchaseAmount) cnt,
					SUM(purchaseAmount/purchaseExchangeRate) total, 
					SUM(purchaseUserCommission/purchaseExchangeRate) totalUser,
					SUM(purchasePartnerCommission/purchaseExchangeRate) totalPartner
			FROM ".T_PURCHASES."
			WHERE partnerID='$partnerID' AND YEAR($purchaseDateField)=YEAR('$date') AND MONTH($purchaseDateField)=MONTH('$date')
			GROUP BY statusID";

	$db->query($sql);
	$stats = array();
	while ( $db->next_record() ){
		$stats [$db->f("statusID" )] = array(
		"cnt" => intval($db->f("cnt")),
		"total" => round(floatval($db->f("total")), 2),
		"totalUser" => round(floatval($db->f("totalUser")), 2),
		"totalPartner" => round(floatval($db->f("totalPartner")), 2)
		);
	}
	return $stats;
}

/**
* Returns list of purchases
 *
 * @param filter $search
 * @param int $page - page number
 * @param int $ppg - per page
 * @return list
 */
function loadPurchasesList($search, $page, $ppg = PPG){
	global $db;

	$sql = "SELECT p.*,u.userEmail,u.userExternalID,m.merchantTitle, n.networkTitle,c.countryCurrencyCode,c.countryTitle,m.merchantParentID
			FROM " . T_PURCHASES . " p
			INNER JOIN " . T_MERCHANTS . " m ON m.merchantID=p.merchantID
			INNER JOIN " . T_COUNTRIES . " c ON m.countryID=c.countryID
			LEFT JOIN " . T_NETWORKS. " n ON m.networkID = n.networkID
			LEFT JOIN " . T_USERS . " u ON u.userID=p.userID
		  WHERE 1=1 $search	";

	$sql_cnt = "SELECT count(p.purchaseID) cnt FROM " . T_PURCHASES . " p
			INNER JOIN " . T_MERCHANTS . " m ON m.merchantID=p.merchantID
		  WHERE 1=1 $search	";

	$purchases = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
	$purchases->loadData();

	return $purchases;
}

/**
 * This function is responsible for recalculate users balances
 */
function recalculateUsersBalances(){
	global $db;

	$db->query("CREATE TEMPORARY TABLE temp (userID int, userBalance float(10,2))");
	$db->query("INSERT INTO temp (
					SELECT u.userID, (IFNULL(invoiceAmount,0) - IFNULL(SUM(paymentAmount),0)) 
					FROM " . T_USERS . " u 
					LEFT JOIN " . T_INVOICES . " i ON (i.userID = u.userID and invoicePaid = 'y') 
					LEFT JOIN " . T_PAYMENTS . " p ON (u.userID = p.userID AND p.pstatusID!='3') 
					GROUP BY userID)");

	$db->query("UPDATE " . T_USERS . " u, temp t SET u.userBalance = t.userBalance WHERE u.userID = t.userID");
	$db->query("DROP TEMPORARY TABLE temp" );
}


//***************************************************************************************************
//****************************** PAYMENT ORIENTED FUNCTIONS *****************************************
//***************************************************************************************************
/**
* Returns list of payments
 *
 * @param $search - filter
 * @param int $page
 * @param int $ppg
 * @return list
 */
function loadPaymentsList($search, $page, $ppg = PPG){
	global $db;

	$sql = "SELECT * FROM " . T_PAYMENTS . " pm
			INNER JOIN " . T_USERS . " u ON (u.userID=pm.userID)
		  WHERE 1=1 $search ";

	$sql_cnt = "SELECT count(pm.paymentID) cnt FROM " . T_PAYMENTS . " pm
			INNER JOIN " . T_USERS . " u ON (u.userID=pm.userID)
			WHERE 1=1 $search";

	$purchases = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg );
	$purchases->loadData();

	return $purchases;
}

/**
 * Returns payments statuses as array
 *
 * @return array
 */
function getPaymentsStatuses(){
	global $db;

	$sql = "SELECT * FROM " . T_PAYMENTS_STATUSES . " ";
	$db->query($sql);
	$statuses = array();
	while ( $db->next_record() ){
		$statuses [$db->f("statusID" )] = $db->f("statusTitle" );
	}
	return $statuses;
}


//************************************************* MERCHANT ORIENTED FUNCTIONS ***********************************************



/**
 * Return array of merchants
 *
 * @return array
 */
function getMerchants(){
	global $db;

	$sql = "SELECT merchantID, merchantTitle FROM " . T_MERCHANTS . " WHERE merchantParentID = 0  AND merchantEnabled='y' ORDER BY merchantTitle";
	$db->query($sql);
	$merchants = array();
	while ( $db->next_record() ){
		$merchants [$db->f("merchantID" )] = $db->f("merchantTitle" );
	}
	return $merchants;
}

/**
 * Returns count of merchants for current partner
 *
 * @param int $partnerID
 * @return int
 */
function getCountMerchants($partnerID){
	global $db;
	$db->query("SELECT COUNT(pm.merchantID) cnt FROM ".T_PARTNERS_MERCHANTS." pm
				INNER JOIN ".T_MERCHANTS." m ON (m.merchantID = pm.merchantID AND m.merchantEnabled='y' AND m.merchantParentID=0)
				WHERE pm.partnerID = '$partnerID'");

	$db->next_record();
	return $db->f("cnt" );
}

/**
 * Returns merchant list with merchant networks
 *
 * @return array
 */
function getMerchantsWithNetworks(){
	global $db;
	$countries = getCountries();

	$sql = "SELECT m.*, networkTitle FROM " . T_MERCHANTS . " m
				LEFT JOIN " . T_NETWORKS . " n ON (n.networkID = m.networkID)
			ORDER BY merchantTitle";
	$db->query($sql);
	$merchants = array();
	while ( $db->next_record()){
		$merchants [$db->f("merchantID" )] = $db->f("merchantTitle" ) . " (" . $db->f("networkTitle" ) . ") /".$countries[$db->f("countryID")]['countryTitle']  ;
	}
	return $merchants;
}



/**
 * Returns list of partner commissions
 */
function loadMerchantPartnersCommissions($merchantID, $page = 1, $ppg = 100, $filter=''){
	global $db;

	$sql = "SELECT p.partnerCommission,ifnull(p.partnerCommission*100/m.merchantCommission,100) as partnerCommissionP,
			   r.partnerID,
			   r.partnerTitle,
			   m.merchantCommission,
			   m.merchantCommissionFixed,
			   m.merchantID,
			   p.partnerCommission  is not null as checked
			FROM " . T_PARTNERS . " r
			LEFT JOIN " . T_PARTNERS_COMMISSIONS . " p ON (r.partnerID = p.partnerID AND p.merchantID='{$merchantID}')
			LEFT JOIN " . T_MERCHANTS . " m ON (m.merchantID = p.merchantID) 
			WHERE 1=1 {$filter}
			ORDER BY r.partnerTitle";

	$sql_cnt = "SELECT count(r.partnerID) cnt
			FROM " . T_PARTNERS . " r
			LEFT JOIN " . T_PARTNERS_COMMISSIONS . " p ON (r.partnerID = p.partnerID)
			WHERE 1=1 {$filter}";

	$comissions = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
	$comissions->loadData();

	return $comissions;
}

/**
 * Returns list of current merchant categories
 *
 * @param int $merchantID
 * @return array
 */
function loadMerchantCategories($merchantID){
	global $db;

	$sql = "SELECT categoryID FROM " . T_MERCHANTS_CATEGORIES . " WHERE (merchantID = '$merchantID')";
	$db->query($sql);

	$categoryIDs = array();
	while($db->next_record()) $categoryIDs[] = $db->f('categoryID');

	return $categoryIDs;
}
/**
 * Save list of merchant categories
 *
 */
function saveMerchantCategories($merchantID, $categoryIDs){
	global $db;

	$db->query("DELETE FROM " . T_MERCHANTS_CATEGORIES . " WHERE (merchantID = '$merchantID')");

	if(is_array($categoryIDs)){
		foreach ($categoryIDs as $categoryID){
			$db->query("INSERT INTO " . T_MERCHANTS_CATEGORIES . " SET categoryID='".intval($categoryID)."', merchantID = '$merchantID'");
		}
	}
	return 1;
}


/**
 * Returns merchantID basing on URL otherwise returns 0;
 *
 * @return int merchantID
 */
function getMerchantByURL($merchantURL){
	global $db;

	//getting the first chunk of merchant title to narrow down the search
	$chunks = preg_split ( '@[-]@', $chunks, - 1, PREG_SPLIT_NO_EMPTY );

	$sql = "SELECT merchantID,merchantTitle FROM " . T_MERCHANTS . " WHERE merchantTitle LIKE '%{$chunks[0]}%' AND merchantEnabled='y'  AND merchantParentID = 0";
	$db->query($sql);

	$merchantID = 0;
	//trying to match in the part
	while ( $db->next_record() ){
		if (makeURL ( $db->f('merchantTitle' ) ) == $merchantURL){
			$merchantID = $db->f('merchantID' );
			break;
		}
	}
	return $merchantID;
}

/**
 * This function perform search categories from database
 */
function searchCategory($partnerID, $subject){
	global $db;
	$db->query("SELECT c.categoryTitle TITLE, c.categoryID ID FROM " . T_CATS . " c
				INNER JOIN " . T_PARTNERS_CATEGORIES . " p ON (c.categoryID = p.categoryID)	
				WHERE ((p.partnerID = $partnerID) AND c.categoryTitle LIKE ('%$subject%'))" );
	$categories = array();
	while($db->next_record()){
		$categories[$db->f("ID" )] = $db->f("TITLE");
	}
	return $categories;
}


//*********************************************** NETWORKS ORIENTED FUNCTIONS *************************************************

/**
 * Returns list of networks
 *
 */
function getNetworks(){
	global $db;
	$db->query("SELECT networkID, networkTitle FROM " . T_NETWORKS." ORDER BY networkTitle");
	$list = array();
	while($db->next_record()){
		$list[$db->f('networkID')] = $db->f('networkTitle');
	}
	return $list;
}


//*********************************************** VOUCHERS ORIENTED FUNCTIONS *************************************************

/**
 * Returns list of vouchers for current merchant
 *
 * @param int $merchantID
 * @return array
 */
function getMerchantVouchers($merchantID){
	global $db;
	$db->query("SELECT v.*,(v.voucherActualFrom > NOW() AND v.voucherActualTo < NOW()) as actual FROM ".T_VOUCHERS." v WHERE merchantID='{$merchantID}'");
	return $db->getArray();
}

function getVouchersDetails($merchantID,$partnerID){
	global $db;
	
	$sql = "SELECT voucherID 
			FROM ".T_VOUCHERS_WEIGHTS." where voucherid in( SELECT voucherid FROM ".T_VOUCHERS." where voucherActualFrom < NOW() AND voucherActualTo > NOW()
			and (partnerID =0 or partnerID = '$partnerID') and merchantID='$merchantID')";	
	$db->query($sql);
	return $db->getArray();
}
//*********************************************** PARTNERS ORIENTED FUNCTIONS *************************************************


//function gives the ability to detect partner by checking the REDIRECT_URL server variable
function getPartnerByURL($redirectURL){
	global $db;
}

function partnerRounding($partnerID, $amount, $fixed, $inPoints){
	global $db;

	$fixed = ($fixed==='y' || $fixed==1)?1:0;
	$inPoints = ($inPoints==='y'||$inPoints==1)?1:0;

	$partner = new dbObject($db, T_PARTNERS, $partnerID);

	if($partner->profile['partnerRoundingFunction']!=''){// partner has own roundeding function
		$functionBody = $partner->profile['partnerRoundingFunction'];
		$partnerRounding=create_function('$amount,$fixed', $functionBody);
		$amount = $partnerRounding($amount, $fixed, 1);
	}else{
		//default alghoritm
		if($inPoints){
			if($fixed){
				$amount = round($amount);
			}else{
				$amount = round($amount);
			}
		}else{
			if($fixed){
				$amount = sprintf('%.2f',$amount);
			}else{
				$amount = round($amount,2);
			}
		}
	}
	return $amount;
}
/**
 * Returns list of partners
 */ 
function getPartners(){
	global $db;

	$sql = "SELECT partnerID, partnerTitle FROM " . T_PARTNERS . " ORDER BY partnerTitle";
	$db->query($sql);
	$partners = array();
	while ( $db->next_record() ){
		$partners [$db->f("partnerID" )] = $db->f("partnerTitle" );
	}
	return $partners;
}

/**
 * Save additional settings for current partner
 *
 * @param array $settings
 */
function savePartnerSettings($settings=array()){
	global $db;

	foreach ($settings as $key => $value){
		$item = new dbObject($db, T_PARTNERS_SETTINGS, '', " AND settingKey='{$key}' AND settingValue='{$value}'");
		$item->profile['settingKey'] = $key;
		$item->profile['settingValue'] = $value;
		$item->saveData();
	}
}
/**
 * Returns additional settings for current partner
 *
 * @param int $partnerID
 */
function loadPartnerSettings($partnerID = 0){
	global $db;

	$db->query("SELECT * FROM (SELECT * FROM ".T_PARTNERS_SETTINGS." WHERE partnerID = 0 OR partnerID = '{$partnerID}'   ORDER BY partnerID DESC) as t GROUP BY t.settingKey ");
	$settings = array();
	foreach($db->getArray() as $item){
		$settings[$item['settingKey']] = $item['settingValue'];
	}
	return $settings;
}

function getPartnerCountries($partnerID){
	global $db;
	$sql = "SELECT DISTINCT m.countryID, c.countryTitle FROM ".T_MERCHANTS." m
			INNER JOIN ".T_PARTNERS_COMMISSIONS." pc ON (pc.partnerID = '$partnerID' AND pc.merchantID = m.merchantID)
			INNER JOIN ".T_COUNTRIES." c ON (c.countryID = m.countryID)";
	$db->query($sql);
	$countries = array();
	while($db->next_record()){
		$countries[$db->f('countryID')] = $db->f('countryTitle');
	}
	return $countries;
}

/**
 * Returns list of invoices for current partner
 */ 
function notifyPartnerTrashholdOverflow($partnerID=0){
	global $db;
	$sql = "SELECT i.*
				FROM ".T_INVOICES." i 
				INNER JOIN ".T_USERS." u ON(i.userID = u.userID) 
				INNER JOIN ".T_GROUPS." g ON (g.groupID = u.groupID) 
			WHERE 1=1 ". ( $partnerID ? " AND i.partnerID = '{$partnerID} ' ":'') ." AND i.invoiceAmount > g.groupThreshold AND i.invoicePaid='n' AND u.userTrashholdNotified='n'";
	$db->query($sql);
	$list = array();
	while ($db->next_record()){
		$list[] = $db->Record;
	}
	return $list;
}
/**
 * Returns operatorID if Login/Password is correct otherwise returns false
 */ 
function operatorAuthorize($operatorLogin, $operatorPassword){
	global $db;
	$sql = "SELECT operatorID FROM " . T_OPERATORS . " WHERE operatorLogin='{$operatorLogin}' AND operatorPassword = md5('{$operatorPassword}')";
	$db->query($sql);
	echo $sql;die;

	if (!$db->next_record()){
		return false; 
	}else{
		return $db->f('operatorID');
	}
}
/**
 * Returns partnerID if partnerLogin/partnerPassword is correct otherwise returns false
 */ 
function partnerAuthorize($partnerLogin, $partnerPassword){
	global $db;
	$sql = "SELECT partnerID FROM " . T_PARTNERS . " WHERE partnerLogin='{$partnerLogin}' AND partnerPassword = md5('{$partnerPassword}')  AND partnerLoginDisabled = 0";
	$db->query($sql);

	if (!$db->next_record()){
		return false; 
	}else{
		return $db->f("partnerID" );
	}
}
/**
 * Returns partnerID if partnerEmail exists
 */
function partnerEmailExists($partnerEmail){
	global $db;
	$db->query("SELECT partnerID FROM " . T_PARTNERS . " WHERE partnerEmail = '{$partnerEmail}' LIMIT 1");
	return $db->next_record();
}
/**
 * Returns partnerID if partnerLogin exists
 */
function partnerLoginExists($partnerLogin){
	global $db;
	$db->query("SELECT partnerID FROM " . T_PARTNERS . " WHERE partnerLogin = '{$partnerLogin}' LIMIT 1");
	return $db->next_record();
}
/**
 * Returns list of partner commissions
 */
function loadPartnerCommissions($partnerID, $page = 1, $ppg = 100, $filter=''){
	global $db;

	$sql = "SELECT p.partnerCommission,ifnull(p.partnerCommission*100/m.merchantCommission,100) as partnerCommissionP,
			   m.merchantTitle,
			   m.merchantCommission,
			   m.merchantCommissionFixed,
			   m.merchantID,
			   m.merchantBanner,
			   p.partnerCommission  is not null as checked
			FROM " . T_MERCHANTS . " m
			LEFT JOIN " . T_PARTNERS_COMMISSIONS . " p ON (m.merchantID = p.merchantID) AND (p.partnerID = '{$partnerID}') 
			LEFT JOIN " . T_PARTNERS . " r ON (r.partnerID = '{$partnerID}')
			WHERE  merchantEnabled='y' AND merchantParentID=0 {$filter}
			ORDER BY m.merchantTitle";

	$sql_cnt = "SELECT count(m.merchantID) cnt
			FROM " . T_MERCHANTS . " m
			LEFT JOIN " . T_PARTNERS_COMMISSIONS . " p ON (m.merchantID = p.merchantID) AND (p.partnerID = '{$partnerID}') 
			LEFT JOIN " . T_PARTNERS . " r ON (r.partnerID = '{$partnerID}')
			WHERE merchantEnabled='y' AND merchantParentID=0  {$filter}
			ORDER BY m.merchantTitle";

	$comissions = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
	$comissions->loadData();

	return $comissions;
}

/**
 * Save partner commissions
 */
function savePartnerCommissions($partnerID, $commissions){
	global $db;
	foreach ($commissions as $k => $v ){
		$sql = "REPLACE INTO " . T_PARTNERS_COMMISSIONS . " SET partnerID='{$partnerID}', merchantID='$k', partnerCommission='$v'";
		$db->query($sql);
	}
}

/**
 * Remove partner commission
 */
function removePartnerCommission($partnerID, $merchantID){
	global $db;
	if($partnerID && $merchantID){
		$sql = "DELETE FROM " . T_PARTNERS_COMMISSIONS . " WHERE partnerID='{$partnerID}' AND merchantID='{$merchantID}'";
		$db->query($sql);
	}
}
/**
 * Returns partner dates
 *
 * @param int $partnerID
 * @return array
 */
function loadPartnerDates($partnerID){
	global $db;

	$sql = "
		SELECT d.*,(pd.partnerID is not null) as checked
		FROM " . T_DATES . " d
		   LEFT JOIN " . T_PARTNERS_DATES . " pd ON (d.dateID = pd.dateID) AND (pd.partnerID = '{$partnerID}')";

	$db->query($sql);

	return $db->getArray();

}
/**
 * Save partner dates
 *
 * @param int $partnerID
 * @param array $dates
 */
function savePartnerDates($partnerID, $dates){
	global $db;
	$sql = "DELETE FROM " . T_PARTNERS_DATES . " WHERE partnerID='{$partnerID}'";
	$db->query($sql);

	foreach ( $dates as $k => $v ){
		$sql = "REPLACE INTO " . T_PARTNERS_DATES . " SET partnerID='{$partnerID}', dateID='$k'";
		$db->query($sql);
	}
}

/**
 * Returns next date for current partner
 *
 * @param int $partnerID
 */
function getNextPartnerDate($partnerID){
	global $db;

	$sql = "SELECT CONCAT(PERIOD_ADD(CONCAT(YEAR(NOW()),LPAD(MONTH(NOW()),2,'0')),(dateDay<DAY(NOW()))),LPAD(dateDay,2,'0')) AS date
			FROM " . T_PARTNERS_DATES . " pd
				INNER JOIN " . T_DATES . " d ON (pd.dateID = d.dateID)
			WHERE 
			(
				(pd.partnerID = '$partnerID')
			)
			HAVING date>CONCAT(YEAR(NOW()),'-',LPAD(MONTH(NOW()),2,'0'),'-',LPAD(DAY(NOW()),2,'0'))	
			LIMIT 1";

	$db->query($sql);
	$db->next_record();

	//formatting the date
	preg_match ( '@(\d{4})(\d{2})(\d{2})@', $db->f('date' ), $parts );

	unset ( $parts [0] );
	$date = implode ( '-', $parts );
	return $date;
}
/**
 * Returns previous date for current partner
 *
 * @param int $partnerID
 */
function getPreviousPartnerDate($partnerID){
	global $db;

	$sql = "SELECT CONCAT(PERIOD_ADD(CONCAT(YEAR(NOW()),LPAD(MONTH(NOW()),2,'0')),
									-(dateDay>DAY(NOW()))), LPAD(dateDay,2,'0')) AS date
			FROM " . T_PARTNERS_DATES . " pd
				INNER JOIN " . T_DATES . " d ON (pd.dateID = d.dateID)
			WHERE 
			(
				(pd.partnerID = '$partnerID')
			)
			HAVING date > CONCAT(	YEAR(NOW()),'-',LPAD(MONTH(NOW()),2,'0'),'-',LPAD(DAY(NOW()),2,'0')	)	
			LIMIT 1";

	$db->query($sql);
	$db->next_record();

	//formatting the date
	preg_match ( '@(\d{4})(\d{2})(\d{2})@', $db->f('date' ), $parts );

	unset ( $parts [0] );
	$date = implode ( '-', $parts );
	return $date;
}

/**
 * Save weights of merchants for current partner (the weight influences on order of display)
 *
 * @param array $weights
 */
function savePartnerMerchantsWeights($partnerID, $weights=array()){
	global $db;
	foreach($weights as $merchantID => $weight){
		logDB($partnerID, '','p','partner changed merchant weight', $merchantID.':'.$weight);
		$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantWeight='$weight' WHERE partnerID='{$partnerID}' AND merchantID='$merchantID' ";
		$db->query($sql);
	}
}

/**
 * Save weights of merchants for current partner (the weight influences on order of display)
 *
 * @param array $weights
 */
function savePartnerMerchantsVouchersWeights($partnerID, $weights=array()){
	global $db;
	foreach($weights as $merchantID => $weight){
		logDB($partnerID, '','p','partner changed merchant weight', $merchantID.':'.$weight);
		$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantWeight='$weight' WHERE partnerID='{$partnerID}' AND merchantID='$merchantID' ";
		$db->query($sql);
	}
}


/**
 * Save weights of featured merchants for current partner (the weight influences on order of display)
 *
 * @param array $weights
 */
function savePartnerFeaturedMerchantsWeights($partnerID, $weights=array()){
	global $db;
	foreach($weights as $merchantID => $weight){
		logDB($partnerID, '','p','partner changed featured merchant weight', $merchantID.':'.$weight);
		$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantFeaturedWeight='$weight' WHERE partnerID='{$partnerID}' AND merchantID='$merchantID' ";
		$db->query($sql);
	}
}

/**
 * Returns categories list with flags 'checked'
 *
 * @return tree as array
 */
function loadPartnerCategories($partnerID, $checked = false, $categoryParentID = 0, $onlyVisible = 1){
	global $db;

	$sql = "SELECT c.*,(p.partnerID is not null) as checked, (cp.partnerID is not null) as visible
			FROM " . T_CATS . " c
			LEFT OUTER JOIN " . T_PARTNERS_CATEGORIES . " p ON (p.partnerID='$partnerID' AND p.categoryID=c.categoryID)
			LEFT OUTER JOIN ".T_CATEGORIES_PARTNERS." cp ON (cp.partnerID = '$partnerID' AND c.categoryID = cp.categoryID )
			HAVING 1=1";

	if($onlyVisible){
		$sql .= " AND visible ";
	}
	if($checked){
		$sql .= " AND checked ";
	}
	if($categoryParentID || $categoryParentID===0){
		$sql .= " AND c.categoryParentID='$categoryParentID'";
	}
	$sql .= " ORDER BY c.categoryTitle";

	$db->query($sql);
	
	$categories = $db->getArray();
	if ($checked || !$onlyVisible){
		//loading categories tree
		foreach ($categories as $k => $v){
			$children = loadPartnerCategories($partnerID, $checked, $v['categoryID'], $onlyVisible);
			if(count($children))
			$categories[$k]['children'] = $children;
		}
	}

	return $categories;
}

/**
 * Returns categories list with parent nodes
 *
 * @return array
 */
function loadPartnerCategoriesWithParent($partnerID, $parentID = 0){
	global $db;
	$sql = "SELECT c.*, count(c1.categoryID)>0 as childrenPresent
			FROM " . T_CATS . " c
			INNER JOIN " . T_PARTNERS_CATEGORIES . " p ON (p.partnerID='$partnerID' AND p.categoryID=c.categoryID)
			LEFT OUTER JOIN " . T_CATS . " c1 ON (c1.categoryParentID=c.categoryID)
			LEFT JOIN " . T_PARTNERS_CATEGORIES . " p1 ON (p1.partnerID='$partnerID' AND p1.categoryID=c1.categoryID)
			WHERE c.categoryParentID=$parentID GROUP BY c.categoryID ";
	$db->query($sql);
	return $db->getarray();
}

/**
 * Check or uncheck category for current partner 
 *
 */
function updatePartnerCategories($partnerID, $categoryID, $checked){
	global $db;

	$sql = "DELETE FROM " . T_PARTNERS_CATEGORIES . " WHERE partnerID='$partnerID' AND categoryID='$categoryID'";
	$db->query($sql);

	if($checked){
		$sql = "INSERT INTO " . T_PARTNERS_CATEGORIES . " SET partnerID='$partnerID',categoryID='$categoryID'";
		$db->query($sql);
		logDB($partnerID,$partnerID,'p','partner activated category', 'Category ID: '.$categoryID, serialize($_SESSION));
	}else{
		logDB($partnerID,$partnerID,'p','partner deactivated category', 'Category ID: '.$categoryID, serialize($_SESSION));
	
	}
}
/**
 * Returns deails list of merchants for current partner
 *
 */
function loadPartnerMerchantsDetailsList($partnerID, $categoryID, $page = 1, $ppg = 100, $sqlFilter = '', $sqlOrderBy = "m.merchantTitle"){
	global $db;

	//getting the overall list of categories under categoryID parent
	$categories = loadCategoriesList($categoryID);
	$categories[] = - 1;

	$sql = "SELECT
					 (c.partnerCommission/get_partner_exchange_rate(1,'$partnerID',m.countryID,m.merchantCommissionFixed)) as partnerCommission,
					   m.merchantID, m.merchantTitle, m.merchantDescription, m.merchantBanner, m.merchantEnabled, m.merchantTrackingURL,
					   m.merchantCommissionFixed, m.merchantURL, m.merchantParentID,
					   m.merchantDisableWhen,
					   m.categoryID, a.categoryTitle,
					   p.merchantRebate,p.merchantWeight,p.merchantFeaturedWeight,
					   ROUND(p.merchantRebate*100,5) as merchantRebateP,
					   (p.merchantID is not null) as checked,
					   COUNT(offers.merchantID) as cnt,
					   p.merchantFeatured,
					   p.merchantFlagged,
					   GROUP_CONCAT(mcl.categoryID) as categoriesIDs,
					   cn.*
				FROM ".T_MERCHANTS." m
				   INNER JOIN ".T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID AND c.partnerID = '{$partnerID}')
				   INNER JOIN " . T_CATS . " a ON(a.categoryID=m.categoryID)	
				   LEFT OUTER JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID AND c.partnerID = p.partnerID)
				   LEFT OUTER JOIN ".T_MERCHANTS." offers ON (m.merchantID = offers.merchantParentID AND offers.merchantActual > NOW() AND offers.partnerID='{$partnerID}' AND offers.merchantEnabled='y')
				   		LEFT OUTER JOIN ".T_PARTNERS_MERCHANTS." p2 ON (p2.merchantID = offers.merchantID AND p2.partnerID = '{$partnerID}')
				   LEFT OUTER JOIN ".T_COUNTRIES." cn ON (cn.countryID = m.countryID)
				   INNER JOIN ".T_PARTNERS." r ON (r.partnerID = c.partnerID)
				   LEFT OUTER JOIN ".T_MERCHANTS_CATEGORIES." mcl ON (m.merchantID = mcl.merchantID)
				   LEFT OUTER JOIN ".T_MERCHANTS_CATEGORIES." mc ON (m.merchantID = mc.merchantID AND mc.categoryID IN (" . implode ( ',', $categories ) . "))
				WHERE (m.categoryID IN (" .implode(',',$categories).")  OR mc.merchantID IS NOT NULL)
					AND (m.partnerID = c.partnerID OR m.partnerID = 0)
					AND m.merchantParentID=0 
					$sqlFilter 
				 GROUP BY m.merchantID  ORDER BY $sqlOrderBy";

	$sql_cnt = "SELECT COUNT(*) as cnt FROM (
					SELECT m.merchantID
				FROM ".T_MERCHANTS." m
				   		LEFT OUTER JOIN ".T_MERCHANTS." offers ON (m.merchantID = offers.merchantParentID AND offers.merchantActual > NOW() AND offers.partnerID='{$partnerID}' AND offers.merchantEnabled='y')
					   	INNER JOIN ".T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID AND c.partnerID = '{$partnerID}')
					   	LEFT OUTER JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID AND c.partnerID = p.partnerID)
					   	INNER JOIN ".T_PARTNERS." r ON (r.partnerID = c.partnerID)
						LEFT OUTER JOIN ".T_MERCHANTS_CATEGORIES." mc ON (m.merchantID = mc.merchantID AND mc.categoryID IN (" . implode ( ',', $categories ) . "))
				WHERE (m.categoryID IN (" .implode(',',$categories).")  OR mc.merchantID IS NOT NULL)
						AND (m.partnerID = c.partnerID OR m.partnerID = 0)
						AND m.merchantParentID=0 
						$sqlFilter 
				) cc";

	$merchants = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg );
	$merchants->loadData();
	
	$categories = getCategories();
	//loading information about special offers from merchants
	foreach ($merchants->list as $k => $v){
		
		if($v['categoriesIDs']){
			foreach (explode(',',$v['categoriesIDs']) as $categoryID){
				$merchants->list[$k]['categories'][$categoryID] = $categories[$categoryID];
			}
		}
		
		if ($v ['cnt'] < 1)
		continue;
		$sql = "
				SELECT c.partnerCommission,
					m.merchantTitle,
				   	m.merchantDescription,
					m.merchantID,
					m.merchantBanner,
					p.merchantRebate,
					m.merchantCommissionFixed,
					ROUND(p.merchantRebate*100,5) as merchantRebateP,
					(p.merchantID is not null) as checked,
					m.merchantURL,
					m.merchantTrackingURL,
					m.partnerID,
					m.merchantActualFrom,
					m.merchantActual,
					m.merchantParentID,
					p.merchantWeight
				FROM merchants m
				   INNER JOIN ".T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
				   LEFT JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
				WHERE 
				   (
					 (m.merchantParentID = '{$v['merchantID']}') AND m.partnerID='{$partnerID}' AND m.merchantActual > NOW()
				   )";
		$db->query($sql);
		$merchants->list[$k]['offers'] = $db->getArray();
	}

	return $merchants;
}

function loadPartnerMerchants($partnerID, $page = 1, $ppg = 100){
	global $db;

	$sql = "SELECT c.partnerCommission,
				   m.*,
				   ROUND(p.merchantRebate*c.partnerCommission*100,5) userRebateP,
				   (c.partnerCommission*merchantRebate) userRebate ,
				   (p.merchantID is not null) as checked,
				   if((ptypeID=2), round(c.partnerCommission*merchantRebate/partnerExchangeRate),0) as userRebatePoints
			FROM ".T_MERCHANTS." m
				INNER JOIN " . T_PARTNERS . " ps ON (ps.partnerID = '{$partnerID}')
				INNER JOIN ". T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
				INNER JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
				LEFT OUTER JOIN ".T_MERCHANTS." offers ON (m.merchantID = offers.merchantParentID)
				WHERE 
				(
				  ((m.partnerID = c.partnerID) or (m.partnerID = 0))
			   		and m.merchantParentID=0 
			   		#and ps.countryID = m.countryID
				)	
				GROUP BY m.merchantID ORDER BY m.merchantTitle	";
	$sql_cnt = "
			SELECT COUNT(m.merchantID) as cnt
			FROM ".T_MERCHANTS." m
			   INNER JOIN ". T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
			   LEFT OUTER JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
			   LEFT OUTER JOIN ".T_MERCHANTS." offers ON (m.merchantID = offers.merchantParentID)
			WHERE 
			(
				((m.partnerID = c.partnerID) or (m.partnerID = 0))
			   	and m.merchantParentID=0
			   )	
			 GROUP BY m.merchantID ";

	$merchants = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg );
	$merchants->loadData();

	//loading information about special offers from merchants
	foreach ( $merchants->list as $k => $v ){
		if ($v ['cnt'] < 1)
		continue;
		$sql = "
				SELECT c.partnerCommission,
					   m.merchantTitle,
					   m.merchantID,
					   m.merchantBanner,
					   p.merchantRebate,
					   m.merchantCommissionFixed,
					   ROUND(p.merchantRebate*100,5) as merchantRebateP,
					   (p.merchantID is not null) as checked,
					   m.merchantURL,
					   m.merchantParentID
					   
				FROM merchants m
				   INNER JOIN partners_commissions c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
				   LEFT OUTER JOIN partners_merchants p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
				WHERE 
				   (
					  (m.merchantParentID = '{$v['merchantID']}') and m.merchantActual>NOW()
				   )";
		$db->query($sql);
		$merchants->list [$k] ['offers'] = $db->getarray();
	}

	return $merchants;
}

/**
 * Returns list of available merchants for current partner
 *
 */
function loadAvailableMerchants($partnerID, $groupID=0, $page = 1, $ppg = 100){
	global $db;

	$sql = "SELECT c.partnerCommission,
				   m.*,
					ROUND(p.merchantRebate*c.partnerCommission*g.groupComission*100,5) userRebateP,
					(c.partnerCommission*merchantRebate) userRebate ,
					(p.merchantID is not null) as checked,
					if((ptypeID=2), round(c.partnerCommission*g.groupComission*merchantRebate/partnerExchangeRate),0) as userRebatePoints
			FROM ".T_MERCHANTS." m
				INNER JOIN " . T_GROUPS . " g ON (g.groupID = '{$groupID}')
				INNER JOIN " . T_PARTNERS . " ps ON (ps.partnerID = '{$partnerID}')
				INNER JOIN ". T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
				INNER JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
				LEFT OUTER JOIN ".T_MERCHANTS." offers ON (m.merchantID = offers.merchantParentID)
				WHERE 
				(
				  ((m.partnerID = c.partnerID) or (m.partnerID = 0))
			   		and m.merchantParentID=0 
			   		#and ps.countryID = m.countryID
				)	
				GROUP BY m.merchantID ORDER BY m.merchantTitle	";

	$sql_cnt = "
			SELECT COUNT(m.merchantID) as cnt
			FROM ".T_MERCHANTS." m
			   INNER JOIN ". T_PARTNERS_COMMISSIONS." c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
			   LEFT OUTER JOIN ".T_PARTNERS_MERCHANTS." p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
			   LEFT OUTER JOIN ".T_MERCHANTS." offers ON (m.merchantID = offers.merchantParentID)
			WHERE 
			( ( (m.partnerID = c.partnerID) or (m.partnerID = 0) ) and m.merchantParentID=0)	
			 GROUP BY m.merchantID ";

	$merchants = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg );
	$merchants->loadData();

	//loading information about special offers from merchants
	foreach ( $merchants->list as $k => $v ){
		if ($v ['cnt'] < 1)
		continue;
		$sql = "
				SELECT c.partnerCommission,
					   m.merchantTitle,
					   m.merchantID,
					   m.merchantBanner,
					   p.merchantRebate,
					   m.merchantCommissionFixed,
					   ROUND(p.merchantRebate*100,5) as merchantRebateP,
					   (p.merchantID is not null) as checked,
					   m.merchantURL,
					   m.merchantParentID
					   
				FROM merchants m
				   INNER JOIN partners_commissions c ON (m.merchantID = c.merchantID) AND (c.partnerID = '{$partnerID}')
				   LEFT OUTER JOIN partners_merchants p ON (m.merchantID = p.merchantID) AND (c.partnerID = p.partnerID)
				WHERE 
				   (
					  (m.merchantParentID = '{$v['merchantID']}') and m.merchantActual>NOW()
				   )";
		$db->query($sql);
		$merchants->list [$k] ['offers'] = $db->getarray();
	}

	return $merchants;
}
/**
 * Save rebate, flag 'featured' and check or uncheck current merchant for partner
 *
 * @param int $partnerID
 * @param int $merchantID
 * @param float $merchantRebate
 * @param 'y'/'n' $merchantFeatured
 * @param int $checked
 */
function updatePartnerMerchant($partnerID, $merchantID, $merchantRebate, $merchantFeatured='n', $merchantFlagged='n', $checked){
	global $db;
	
	if($checked){
		$db->query("SELECT * FROM " . T_PARTNERS_MERCHANTS . " WHERE partnerID='$partnerID' AND merchantID='$merchantID'");
			$db->next_record();
			if($db->Record['merchantID']){// record is exist
				$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantRebate='$merchantRebate',merchantFeatured='".($merchantFeatured=='y'?'y':'n')."',merchantFlagged='".($merchantFlagged=='y'?'y':'n')."' , merchantCorrected=0 WHERE partnerID='$partnerID' AND merchantID='$merchantID'";
			}else{
				$sql = "INSERT INTO " . T_PARTNERS_MERCHANTS . " SET partnerID='$partnerID',merchantID='$merchantID',merchantRebate='$merchantRebate',merchantFeatured='".($merchantFeatured=='y'?'y':'n')."',merchantFlagged='".($merchantFlagged=='y'?'y':'n')."' , merchantCorrected=0";
			}
		$db->query($sql);
	}else{//delete
		$sql = "DELETE FROM " . T_PARTNERS_MERCHANTS . " WHERE partnerID='$partnerID' AND merchantID='$merchantID'";
		$db->query($sql);
	}
}
/**
 * Set main rebate value for merchants list for current partner
 *
 */
function updatePartnerMerchantsAll($partnerID, $merchantIDs = array(), $merchantRebate){
	global $db;
	if (count($merchantIDs)){
		$sql = "SELECT * FROM " . T_PARTNERS_MERCHANTS . " WHERE partnerID='$partnerID' AND merchantID IN (" . implode ( ', ', $merchantIDs ) . ")";
		$db->query($sql);
		$merchants = $db->getarray();
		$exists_merchants = array();
		foreach($merchants as $item){
			$exists_merchants[] = $item['merchantID'];
		}
		foreach($merchantIDs as $merchantID){
			if(in_array($merchantID, $exists_merchants)){
				$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantRebate='$merchantRebate' WHERE partnerID='$partnerID' AND merchantID='$merchantID'";
			}else{
				$sql = "INSERT INTO " . T_PARTNERS_MERCHANTS . " SET merchantRebate='$merchantRebate',partnerID='$partnerID',merchantID='$merchantID'";
			}
			$db->query($sql);
		}
	}
}


/**
 * Returns detailed information about the merchant, including 
 * the special offer if present (for user)
 * @param int $partnerID
 * @param int $merchantID
 * @param int $groupID - user group ID
 * @return array
 */
function loadMerchantDetails($partnerID, $merchantID, $groupID, $filter = " AND merchantEnabled='y' "){
	global $db;

	$sql = "SELECT c.partnerCommission,
				m.*, ct.categoryTitle, p.merchantRebate,
				ROUND(p.merchantRebate*c.partnerCommission*g.groupComission*100,2) userRebateP,
				(c.partnerCommission*merchantRebate) userRebate,
				ROUND(c.partnerCommission/m.merchantCommission,2) as partnerRebate,
				if((a.ptypeID=2),round(c.partnerCommission*g.groupComission*merchantRebate/get_partner_exchange_rate(partnerExchangeRate,'$partnerID',m.countryID,m.merchantCommissionFixed),2),0) as userRebatePoints
			FROM " . T_MERCHANTS . " m
				INNER JOIN " . T_CATS. " ct ON (ct.categoryID=m.categoryID)
				INNER JOIN " . T_GROUPS . " g ON (g.groupID = '$groupID')
				INNER JOIN " . T_PARTNERS_COMMISSIONS . " c ON (m.merchantID = c.merchantID) AND (c.partnerID = '$partnerID')
				INNER JOIN " . T_PARTNERS_MERCHANTS . " p ON (m.merchantID = p.merchantID) AND (p.partnerID = '$partnerID')
				INNER JOIN " . T_PARTNERS . " a ON (a.partnerID = '$partnerID')
			WHERE (m.merchantID = '$merchantID'  {$filter})";
	$db->query($sql);
	$db->next_record();
	$merchant = $db->Record;

	//trying to load merchant's special offer
	$sql = "SELECT merchantID
			FROM " . T_MERCHANTS . " 
			WHERE merchantParentID='{$merchant['merchantID']}' AND merchantParentID != merchantID AND merchantParentID!=0 
					AND partnerID = '$partnerID' AND LOCATE('S',merchantFlags)";
	$db->query($sql);

	if ($db->next_record()){
		$offer = loadMerchantDetails($partnerID, $db->f("merchantID"),$groupID);
		$copy = $merchant;
		unset($copy['offer']);
		$merchant['offer'] = $copy;
		
		$merchant['offer']['merchantID'] = $offer['merchantID'];
		$merchant['offer']['merchantCommission'] = $offer['merchantCommission'];
		$merchant['offer']['merchantDescription'] = $offer['merchantDescription'];
		$merchant['offer']['partnerID'] = $offer['partnerID'];
		$merchant['offer']['merchantParentID'] = $offer['merchantParentID'];
		$merchant['offer']['merchantActualFrom'] = $offer['merchantActualFrom'];
		$merchant['offer']['merchantActual'] = $offer['merchantActual'];
		$merchant['offer']['merchantRebate'] = $offer['merchantRebate'];
		$merchant['offer']['userRebateP'] = $offer['userRebateP'];
		$merchant['offer']['userRebate'] = $offer['userRebate'];
		$merchant['offer']['userRebatePoints'] = $offer['userRebatePoints'];
		$merchant['offer']['merchantTrackingURL'] = $offer['merchantTrackingURL'];
	}
	return $merchant;
}






/**
 * Returns an array of merchants for the partner by the category specified 
 * THIS FUNCTION IS ABSOLUTELY IDENTICAL TO USER FUNCTION loadMerchantsList ON THE PARTNER SITE
 * 
 * @param int $partnerID
 * @param int $groupID - user group ID
 * @param int $page - page number
 * @param int $ppg - items per page
 * @param string $orderBy sorting key, defines the items shown at the top:
 *  - latest - recently added
 *  - amount - with the biggest fixed commission 
 *  - amountPercent - with the biggest percentage
 *  - voucher - only the one with vouchers
 *	- popular - popularity (number of clicks)
 * 
 * @return array
 */
function loadMerchantsList($partnerID, $categoryID, $groupID, $page, $ppg = 10, $filter = '', $orderBy='name', $sqlFilter ='', $sqlOrder='', $sqlExtraFilter=''){
	global $db;

	if($cache = getCache(__FUNCTION__.serialize(func_get_args()))){
		return unserialize($cache);
	}else{
		//getting the overall list of categories under categoryID parent
		$categories = loadCategoriesList($categoryID);
		$categories[] = 0;
		$sqlHaving = $sqlJoin = $sqlWhere = '';

		if($sqlFilter==''){
			switch ($filter){
				case 'voucher': $sqlHaving=" AND voucher "; 
								$sqlJoin = " INNER JOIN " . T_VOUCHERS . " v ON (m.merchantID = v.merchantID) ";
								$sqlJoin .= " INNER JOIN " . T_VOUCHERS_WEIGHTS . " w ON (v.voucherID = w.voucherID) ";
								//$sqlWhere = " AND LOCATE('V', m.merchantFlags) ";
								$sqlOrder = 'w.voucherWeight';
								break;
								
				case 'offer':	$sqlHaving=" AND special";
								$sqlOrder = 'p.merchantWeight'; 
								break;
								
				case 'featured':$sqlWhere = " AND p.merchantFeatured='y' ";
								$sqlOrder = 'p.merchantFeaturedWeight, m.merchantAdded DESC';
								break;
			}
		}else{
			$sqlWhere = $sqlFilter;
		}
		
		if($sqlOrder==''){
			switch ($orderBy){
				case 'latest':	$sqlOrder = 'm.merchantAdded DESC'; break;
				case 'amount':	$sqlOrder = 'm.merchantCommissionFixed DESC, userRebate DESC'; break;
				case 'amountPercent':	$sqlOrder = " (IF(m.merchantCommissionFixed='y',0,1)) DESC, userRebateP DESC"; break;
				case 'voucher':	$sqlOrder = "voucher DESC"; $sqlHaving=" AND voucher "; break;
				case 'popular':	$sqlOrder = 'p.merchantClicks DESC'; break;
				// only for voucher filter
				case 'voucherRecentlyAdded':	$sqlOrder = 'v.voucherID DESC'; break;
				case 'voucherSoonToExpire':	$sqlOrder = 'v.voucherActualTo ASC'; break;
				case 'voucherLastToExpire':	$sqlOrder = 'v.voucherActualTo DESC'; break;
				case 'name': default: $sqlOrder = 'm.merchantTitle';
			}
		}
		
		$sql = "SELECT c.partnerCommission, a.*, m.*,
						CONCAT(m.merchantFlags,p.merchantFlags) as merchantFlags,
					   ((isnull(offers.merchantID) AND m.merchantParentID=0) OR (LOCATE('S',m.merchantFlags) AND m.merchantParentID!=0) ) as visible,
					   (LOCATE('V',m.merchantFlags) OR LOCATE('V',p.merchantFlags)) as voucher,
					   LOCATE('S',m.merchantFlags) as special,
					   ROUND(p.merchantRebate*c.partnerCommission*g.groupComission*100,2) userRebateP,
					   (c.partnerCommission*merchantRebate) userRebate,
					   if((ptypeID=2), round(c.partnerCommission*g.groupComission*merchantRebate/get_partner_exchange_rate(partnerExchangeRate,'$partnerID',m.countryID,m.merchantCommissionFixed),2), 0) as userRebatePoints
				FROM " . T_MERCHANTS . " m
						LEFT JOIN " . T_MERCHANTS . " offers ON (offers.merchantParentID = m.merchantID AND LOCATE('S',offers.merchantFlags) AND offers.partnerID='$partnerID' AND offers.merchantEnabled='y')
						INNER JOIN " . T_GROUPS . " g ON (g.groupID = '$groupID')
						INNER JOIN " . T_CATS . " a ON (a.categoryID=m.categoryID) 
						INNER JOIN " . T_PARTNERS_MERCHANTS . " p ON(p.partnerID='$partnerID' AND m.merchantID=p.merchantID) 
						INNER JOIN " . T_PARTNERS_COMMISSIONS . " c ON (m.merchantID = c.merchantID) AND (c.partnerID = '$partnerID')
						INNER JOIN " . T_PARTNERS . " r ON (r.partnerID = '$partnerID')
						 ".$sqlJoin." 
						LEFT JOIN ". T_MERCHANTS_CATEGORIES ." mc ON (m.merchantID = mc.merchantID AND mc.categoryID IN (" .implode(',',$categories). "))
				WHERE 1=1 {$sqlWhere} AND ((m.partnerID = '$partnerID') OR (m.partnerID = 0)) 
						AND (m.categoryID IN(" .implode(',',$categories). ") OR (mc.merchantID IS NOT NULL))
						AND m.merchantEnabled='y' {$sqlExtraFilter}
				GROUP BY m.merchantID HAVING (visible {$sqlHaving}) ORDER BY $sqlOrder";

		$sql_cnt = "SELECT COUNT(x) as cnt FROM(
						SELECT m.merchantID x,
						((isnull(offers.merchantID) AND m.merchantParentID=0) OR (LOCATE('S',m.merchantFlags) AND m.merchantParentID!=0) ) as visible,
					   	LOCATE('S',m.merchantFlags) as special,
						(LOCATE('V',m.merchantFlags) OR LOCATE('V',p.merchantFlags)) as voucher
					FROM " . T_MERCHANTS . " m
						LEFT JOIN " . T_MERCHANTS . " offers ON (offers.merchantParentID = m.merchantID AND LOCATE('S',offers.merchantFlags) AND offers.partnerID='$partnerID' AND offers.merchantEnabled='y')
						INNER JOIN " . T_CATS . " a ON(a.categoryID=m.categoryID)
						INNER JOIN " . T_GROUPS . " g ON (g.groupID = '$groupID')
						INNER JOIN " . T_PARTNERS_MERCHANTS . " p ON(p.partnerID='$partnerID' AND m.merchantID=p.merchantID) 
						INNER JOIN " . T_PARTNERS_COMMISSIONS . " c ON (m.merchantID = c.merchantID) AND (c.partnerID = '$partnerID')
						INNER JOIN " . T_PARTNERS . " r ON (r.partnerID = '$partnerID')
						 ".$sqlJoin." 
						LEFT JOIN ".T_MERCHANTS_CATEGORIES." mc ON (m.merchantID = mc.merchantID AND mc.categoryID IN (" .implode(',',$categories). "))
					WHERE 1=1 {$sqlWhere} AND ((m.partnerID = '$partnerID' ) OR (m.partnerID = 0)) 
						AND (m.categoryID IN(" .implode(',',$categories). ") OR (mc.merchantID IS NOT NULL))
						AND m.merchantEnabled='y' {$sqlExtraFilter}
					GROUP BY m.merchantID HAVING visible {$sqlHaving}) t ";	
		$merchants = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
		$merchants->loadData();
		setCache(__FUNCTION__.serialize(func_get_args()), serialize($merchants));
		return $merchants;
	}
}

/**
* Returns list of active vouchers for the merchant
* THIS FUNCTION IS ABSOLUTELY IDENTICAL TO USER FUNCTION getMerchantVouchers ON THE PARTNER SITE
* @param int $merchantID
* @return array
*/
function getMerchantVouchersForUser($merchantID,$partnerID,$orderBy="voucherID DESC"){
	global $db;
	$sql = "SELECT v.*,(v.voucherActualFrom < NOW() AND v.voucherActualTo > NOW()) as actual 
					FROM ".T_VOUCHERS." v 
				WHERE merchantID='$merchantID'
					AND ((SELECT LOCATE('V',merchantFlags) FROM ".T_MERCHANTS." WHERE merchantID = '$merchantID')
						OR (v.partnerID = '$partnerID' AND (SELECT LOCATE('V',merchantFlags) FROM ".T_PARTNERS_MERCHANTS." WHERE merchantID = '$merchantID' AND partnerID = '$partnerID')))	
				HAVING actual ORDER BY ".$orderBy;

	$db->query($sql);

	return $db->getArray();
}









/**
 * function loads information about the actual exchange rate for the currency
 *
 * @param unknown_type $partnerID
 * @param unknown_type $countryID
 * @return unknown
 */


function getPartnerExchangeRate($partnerID, $countryID, $date=''){
	global $db;

	$sql="SELECT prExchangeRate FROM ".T_PARTNERS_RATES." pr WHERE pr.partnerID='$partnerID' AND pr.countryID='$countryID'";
	
	if($date){
		$sql.=" AND prAdded<'$date' ";
	}
	
	$sql.=' ORDER BY prAdded DESC LIMIT 1';
	$db->query($sql);

	if($db->next_record()){
		$rate=$db->f('prExchangeRate');
	}else{
		$rate=1;
	}

	return $rate;
}

/**
 * function determines partner's active points to cashback rate value
 *
 * @param unknown_type $partnerID
 * @param unknown_type $purchaseAdded
 * @return unknown
 */

function getPartnerPurchaseRate($partnerID,$purchaseAdded){
	global $db;
	
	$sql="select rateExchangeRate from ".T_PARTNERS_POINTS_RATES." where rateAdded<='$purchaseAdded' and partnerID='$partnerID' order by rateAdded DESC limit 1";
	$db->query($sql);
	
	if(!$db->next_record()){
		//falling back to default partner information
		$partner=new dbObject($db,T_PARTNERS,$partnerID);
		$rate=$partner->profile['partnerExchangeRate'];
	}
	else{
		$rate=$db->f('rateExchangeRate');
	}
	
	return $rate;	
}

/**
 * function loads the list of actual partner currency exchange rates
 *
 * @param unknown_type $partnerID
 * @return unknown
 */

function loadPartnerExchangeRates($partnerID){
	global $db;
	
	$sql="SELECT pr.prExchangeRate, 
					pr.countryID
				FROM (select prExchangeRate, countryID, prAdded from ".T_PARTNERS_RATES." where partnerid='$partnerID' order by prAdded desc ) pr
				group by countryid";
	$db->query($sql);
	
	return $db->getArray();

}


//*********************************************** USERS ORIENTED FUNCTIONS ****************************************************


/**
 * Returns userID if partnerLogin/partnerPassword is correct otherwise returns false
 */
function userAuthorize($userEmail, $userPassword){
	global $db;
	$db->query("SELECT userID FROM ".T_USERS." WHERE userEmail='{$userEmail}' AND userPassword = md5('{$userPassword}')");

	if ($db->next_record()){
		$db->query("UPDATE ".T_USERS." SET userLoginFaults = 0 WHERE userEmail='{$userEmail}'");
		return $db->f("userID" );
	}else{
		$db->query("UPDATE ".T_USERS." SET userLoginFaults = userLoginFaults+1 WHERE userEmail='{$userEmail}'");
		return false;
	}
}
/**
 * Returns userID if userEmail exists
 */
function userEmailExists($userEmail){
	global $db;
	$db->query("SELECT userID FROM " . T_USERS . " WHERE userEmail = '{$userEmail}' LIMIT 1" );
	return $db->next_record();
}
/**
 * Return count of fault login attempts for current user
 */
function getLoginAttempts($userID){
	global $db;
	$db->query("SELECT userLoginFaults FROM ".T_USERS." WHERE userID = '{$userID}'");
	$db->next_record();
	return $db->Record['userLoginFaults'];
}
/**
 * Save user disabled reason and disable current user
 *
 * @param int $userID
 * @param string $userDisabledReason
 */
function userDisable($userID, $userDisabledReason){
	global $db;
	$db->query("UPDATE ".T_USERS." SET userEnabled='n', userDisabledReason = '{$userDisabledReason}' WHERE userID='{$userID}'");
}
/**
 * Enable current user
 */
function userEnable($userID){
	global $db;
	$db->query("UPDATE ".T_USERS." SET userEnabled='y', userLoginFaults='0',userDisabledReason = NULL WHERE userID='{$userID}'");
}
/**
 * Returns userID if userBirths is correct for current userEmail
 */
function userEmailBirthCheck($userEmail, $userBirth){
	global $db;
	$db->query("SELECT userID
				  FROM " . T_USERS . "
				  WHERE userEmail = '{$userEmail}' AND userBirth = '{$userBirth}'
				  LIMIT 1");
	$db->next_record();
	return $db->Record['userID'];
}
/**
 * Returns userID if userLogin exists
 */
function userLoginExists($userLogin){
	global $db;
	$db->query("SELECT userID
				  FROM " . T_USERS . "
				  WHERE userLogin = '{$userLogin}'
				  LIMIT 1" );
	return $db->next_record();
}
/**
 * Returns user profile by user tracking ID
 *
 * @return array
 */
function findUserByTrackingID($partnerID, $userTrackingID){
	global $db;

	$sql = "SELECT * FROM " . T_USERS . " WHERE userTrackingID='{$userTrackingID}' AND partnerID='{$partnerID}' LIMIT 1";
	$db->query($sql);

	return ($db->next_record() ? $db->Record : 0);
}
/**
 * Returns userID by userEmail
 * 
 * @return userID
 */
function getUserID($userEmail){
	global $db;
	$db->query("SELECT userID
			     FROM " . T_USERS . "
			     WHERE userEmail = '{$userEmail}'
			     LIMIT 1" );
	$db->next_record();
	return intval($db->f("userID"));
}

/**
 * Returns purchases for current user
 *
 * @return array of purchases
 */
function loadUserPurchases($partnerID, $userID, $limit = 50, $statusID = 0, $fromDate = 0, $toDate = 0, $exchangeRate = 0){
	global $db;
	$_sql = '';

	if ($statusID)
	$_sql .= " AND p.statusID='$statusID' ";
	if ($fromDate)
	$_sql .= " AND p.purchaseDate>'$fromDate'";
	if ($toDate)
	$_sql .= " AND p.purchaseDate<'$toDate'";

	$exchangeRate = floatval($exchangeRate);
	$sql = "SELECT merchantTitle,
				   statusTitle, m.merchantCommissionFixed,
				   p.*,purchaseUserCommissionPoints as purchaseUserPoints
			FROM ".T_PURCHASES." p
			   INNER JOIN " . T_PSTATUSES . " s ON (p.statusID = s.statusID)
			   INNER JOIN " . T_MERCHANTS . " m ON (p.merchantID = m.merchantID)
			   INNER JOIN " . T_PARTNERS . " a ON (a.partnerID=p.partnerID)
			WHERE 
			   ((p.partnerID = '$partnerID')
			   and 
				  (p.userID = '$userID')
			   ) $_sql
			ORDER BY purchaseDate DESC";

	$db->query($sql);
	$purchases = $db->getArray();

	foreach($purchases as $k=>$purchase){
		$purchases[$k]['purchaseUserPoints'] = partnerRounding($partnerID, $purchase['purchaseUserPoints'], $purchase['merchantCommissionFixed']);
	}

	return $purchases;
}

/**
 * Returns purchases for current user
 *
 * @return array of purchases
 */
function loadPurchases($partnerID, $filter = '', $page, $ppg = PPG, $statusID = 0, $exchangeRate = 0){
	global $db;

	if ($statusID)
	$filter .= " AND p.statusID='$statusID' ";
	$exchangeRate = floatval ( $exchangeRate );

	$sql = "SELECT merchantTitle, m.merchantID,m.merchantParentID, m.merchantCommissionFixed, statusTitle, p.*,
			u.*, purchaseUserCommissionPoints as purchaseUserPoints,
			m.countryID, c.*
			FROM ".T_PURCHASES." p
			   INNER JOIN " . T_PSTATUSES . " s ON (p.statusID = s.statusID)
			   INNER JOIN " . T_MERCHANTS . " m ON (p.merchantID = m.merchantID)
			   INNER JOIN " . T_COUNTRIES ." c ON (c.countryID = m.countryID)
			   INNER JOIN " . T_PARTNERS . " a ON (a.partnerID=p.partnerID)
			   LEFT JOIN ".	T_USERS ." u ON (u.userID = p.userID)
			WHERE 
			   ((p.partnerID = '$partnerID') $filter) 
			ORDER BY purchaseDate DESC";
			$sql_cnt = "SELECT COUNT(p.purchaseID) cnt
			FROM ".T_PURCHASES." p
			   INNER JOIN " . T_PSTATUSES . " s ON (p.statusID = s.statusID)
			   INNER JOIN " . T_MERCHANTS . " m ON (p.merchantID = m.merchantID)
			   INNER JOIN " . T_COUNTRIES ." c ON (c.countryID = m.countryID)
			   INNER JOIN " . T_PARTNERS . " a ON (a.partnerID=p.partnerID)
			   LEFT JOIN ".	T_USERS ." u ON (u.userID = p.userID)
			WHERE 
			   ((p.partnerID = '$partnerID') $filter)";

			$list = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
			$list->loadData();

			foreach($list->list as $k=>$purchase){
				$list->list[$k]['purchaseUserPoints'] = partnerRounding($partnerID, $purchase['purchaseUserPoints'], $purchase['merchantCommissionFixed']);
			}
			return $list;
}

/**
 * Returns letters of userSurname first letters
 *
 * @return array
 */
function getUsersLetters($partnerID){
	global $db;
	$sql = "SELECT UPPER(LEFT(userSurname,1)) as letter
				FROM " . T_USERS . " u
				WHERE u.partnerID = '$partnerID' GROUP BY letter";

	$db->query($sql);
	$letters = array();
	while ($db->next_record()){
		$letters[$db->f("letter" )] = $db->f("letter");
	}
	return $letters;
}
/**
 * Returns invoices for current user
 *
 */
function loadUserInvoices($partnerID, $userID, $invoicePaid = '', $exchangeRate = 0){
	global $db;
	$sql = "SELECT i.*,u.*,invoiceAmountPoints invoicePoints FROM " . T_INVOICES . " i
			INNER JOIN " . T_USERS . " u ON (i.userID=u.userID AND i.partnerID=u.partnerID)
			INNER JOIN " . T_GROUPS . " g ON (u.groupID = g.groupID)
			WHERE i.partnerID='$partnerID' AND i.userID='$userID'";
	if ($threshold > 0){
		$sql .= " AND invoiceAmount>=g.groupThreshold ";
	}

	if ($invoicePaid){
		$sql .= " AND invoicePaid='$invoicePaid'";
	}

	$sql .= " ORDER BY invoiceDate DESC";
	$db->query($sql);

	$invoices = $db->getarray();

	//loading purchases per each invoice
	foreach ( $invoices as $k => $v ){
		if ($v ['invoicePaid'] != 'y')
		continue;
		$sql = "
		SELECT i.invoiceID,(i.invoiceDate) invoiceDateTo,ifnull((select i2.invoiceDate FROM " . T_INVOICES . " i2 where i2.invoiceDate<i.invoiceDate AND i2.userID=i.userID AND i2.partnerID=i.partnerID limit 1),FROM_UNIXTIME(0)) invoiceDateFrom
		FROM " . T_INVOICES . " i
		WHERE i.invoiceID='{$v['invoiceID']}'";
		$db->query($sql);
		$db->next_record();
		//now need to load purchases inside the
		$purchases = loadUserPurchases ( $partnerID, $userID, 10000, 2, $db->f('invoiceDateFrom' ), $db->f('invoiceDateTo' ), $exchangeRate );
		$invoices [$k] ['purchases'] = $purchases;
	}
	return $invoices;
}
/**
 * Returns count of users for current partner
 * 
 * @return int
 */
function getUsersCount($partnerID){
	global $db;
	$sql = "SELECT COUNT(userID) cnt
				FROM " . T_USERS . " u
				WHERE (u.partnerID = '$partnerID') AND (u.userEnabled = 'y') ";
	$db->query($sql);
	$db->next_record();
	return $db->f("cnt" );
}

/**
 * Send email when purchase changes the status
 *
 * @param int $purchaseID
 */
function sendPurchaseStatusChangeEmail($purchaseID){
	global $db, $smarty;

	//preparing the marketing block
	$marketingBlock=getEmailMarketingBlock($purchaseID);
	$smarty->assign('marketingBlock',$marketingBlock);
	
	$purchase=new dbObject($db,T_PURCHASES,$purchaseID);
	$smarty->assign('purchase', $purchase->profile);

	$partner=new stdClass();
	$partner->profile = getSiteInfo($purchase->profile['partnerID']);
	$smarty->assign('partner', $partner->profile);

	if( ($partner->profile['partnerPendingNotification'] && $purchase->profile['statusID']==1) ||
		($partner->profile['partnerApprovedNotification'] && $purchase->profile['statusID']==2)){ // if user email notifacations is allowed for this partner

		$merchant = new dbObject( $db, T_MERCHANTS, $purchase->profile['merchantID']);
		$smarty->assign('merchant', $merchant->profile);

		$user = new dbObject($db, T_USERS, $purchase->profile['userID']);
		$smarty->assign('user', $user->profile );
		$userGroup = new dbObject($db, T_GROUPS, $user->profile['groupID']);
		$smarty->assign('threshold', $userGroup->profile['groupThreshold']);

		$invoice = new dbObject($db, T_INVOICES, '', " AND userID={$purchase->profile['userID']} AND invoicePaid='n'");
		$smarty->assign('userCurrentBalance', floatval($invoice->profile['invoiceAmount']));

		$template = 'purchase.status.email.html';
		$templateAlternative = $purchase->profile['partnerID'].'_purchase.status.email.html';
		if(file_exists("{$smarty->template_dir}/$templateAlternative")) $template = $templateAlternative;

		$body = $smarty->fetch($template);
		
		$subject = getSubject($body);
	//	mailHtml('x000xx@gmail.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
	//	mailHtml('nekrasov_alex@hotmail.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
	  mailHtml('litstas@gmail.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
	  
	//  mailHtml('kirk.peterson@core8.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
	//  mailHtml('martin.harvey@core8.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
//	  mailHtml('martinj_harvey@hotmail.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
//	  mailHtml('biggestapple@gmail.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
 // mailHtml('sameer.kelkar@globallogic.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");
 // mailHtml('ashwini.sane@globallogic.com',$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");			  
		mailHtml($user->profile['userEmail'],$subject, $body, $partner->profile['partnerEmailStatus'], "{$partner->profile['partnerTitle']} support");

	}
}

/**
 * returns the HTML for the marketing block added at the end of the purchase status email
 *
 * @param int $purchaseID
 * @return string
 */

function getEmailMarketingBlock($purchaseID,$limit=6){
	global $db, $smarty;

	$purchase=new dbObject($db,T_PURCHASES,$purchaseID);
	$user = new dbObject($db, T_USERS,$purchase->profile['userID']);
	$merchant = new dbObject($db, T_MERCHANTS,$purchase->profile['merchantID']);
	$partnerProfile = getSiteInfo($purchase->profile['partnerID']);
	$smarty->assign('partner',$partnerProfile);
	$groupID = getDefaultGroup($purchase->profile['partnerID']);
	
	$block = '';
	if($partnerProfile['partnerEmailStatusOptions']==3){
		$block=uhsc($partnerProfile['partnerEmailStatusExtra']);
	}else{
		if($partnerProfile['partnerEmailStatusOptions']==1 || $partnerProfile['partnerEmailStatusOptions']==4){
			//loading the list of merchants which could be interesting
			$merchants = loadMerchantsList($purchase->profile['partnerID'],$merchant->profile['categoryID'],$user->profile['groupID'],1,$limit,'','popular','',''," AND m.merchantID!='{$purchase->profile['merchantID']}'");
			$smarty->assign('recentlyClickedMerchants',$merchants->list);
		}

		if($partnerProfile['partnerEmailStatusOptions']==2 || $partnerProfile['partnerEmailStatusOptions']==4){
			$exceptions = array(0);
			foreach($merchants->list as $v) $exceptions[] = $v['merchantID'];
			
			//loading the list of special offers for the merchants of interest
			$db->query("SELECT GROUP_CONCAT(categoryID) ids FROM (SELECT merchantID FROM ".T_CLICKS." WHERE userID='{$purchase->profile['userID']}' ORDER BY clickDate DESC limit 10) c INNER JOIN ".T_MERCHANTS." m ON c.merchantID=m.merchantID");
			$db->next_record();
			$ids = $db->f('ids') ? $db->f('ids') : '0';
			$sqlExtra = " AND m.merchantID!='{$purchase->profile['merchantID']}' AND m.merchantID NOT IN (".implode(',',$exceptions).") AND a.categoryID in ($ids)";
			$merchants = loadMerchantsList($purchase->profile['partnerID'],'0',$user->profile['groupID'],1,$limit,'offer','','','',$sqlExtra);
			
			if(!count($merchants->list)){
				//else loading the overall list of special offers
				$sqlExtra = " AND m.merchantID!='{$purchase->profile['merchantID']}' AND m.merchantID NOT IN (".implode(',',$exceptions).") ";
				$merchants = loadMerchantsList($purchase->profile['partnerID'],'0',$user->profile['groupID'],1,$limit,'','latest','','',$sqlExtra);
			}
			$smarty->assign('specialOffersMerchants',$merchants->list);
		}

		$template = 'purchase.marketing.email.html';
		$templateAlternative = $purchase->profile['partnerID'].'_purchase.marketing.email.html';
		
		if(file_exists("{$smarty->template_dir}/$templateAlternative")) $template = $templateAlternative;
		$block = $smarty->fetch($template);		
	}
	
	return $block;
}

/**
 * Returns array of users email
 *
 */
function getUsersEmails($partnerID, $ageFrom = 18, $ageTo = 99, $gender = 'all'){
	global $db;
	$filter = " AND partnerID='$partnerID' AND userEnabled='y'";

	switch($gender){
		case 'm': $filter .= " AND userGender='m'"; break;
		case 'f': $filter .= " AND userGender='f'"; break;
	}
	if ($ageFrom&&$ageTo){
		$filter .= " AND (YEAR(NOW()) - userBirth) > '$ageFrom' AND (YEAR(NOW()) - userBirth) < '$ageTo'";
	}
	$sql = "SELECT userEmail FROM ".T_USERS." WHERE userEmail!='' ".$filter;
	$db->query($sql);
	$emailsList = array();
	while($db->next_record()){
		$emailsList[] = $db->Record['userEmail'];
	}
	return $emailsList;
}




//*********************************************** CLICKS ORIENTED FUNCTIONS ***************************************************

/**
 * Returns list of clicks
 *
 * @return array
 */
function loadClicksList($partnerID = 0, $filter, $page, $ppg){
	global $db;

	$sql = "SELECT c.clickID,
				   c.partnerID,
				   c.userID,
				   c.merchantID,
				   c.clickDT,
				   c.clickDate,
				   c.clickTrackingID,
				   c.clickRebate,
				   m.merchantTitle,
				   u.userEmail,
				   u.userName,
				   u.userSurname, purchaseID
			FROM ".T_CLICKS." c
			   INNER JOIN ".T_MERCHANTS." m ON (c.merchantID = m.merchantID)
			   LEFT JOIN ".T_USERS." u ON (c.userID = u.userID)
			WHERE 1=1 ".$filter;

	$sql_cnt = "
			SELECT count(c.clickID) cnt
			FROM  ".T_CLICKS." c
			   INNER JOIN ".T_MERCHANTS." m ON (c.merchantID = m.merchantID)
			   LEFT JOIN ".T_USERS." u ON (c.userID = u.userID)
			WHERE 1=1 ".$filter;

	if($partnerID){
		$sql .= " AND c.partnerID='$partnerID'";
		$sql_cnt .= " AND c.partnerID='$partnerID'";
	}


	$sql .= " ORDER BY clickDate DESC";


	$clicks = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg );
	$clicks->loadData();

	return $clicks;
}

//*********************************************** USER DESTINATIONS ****************************************************

/**
 * Returns the list of active payment destinations for the user
 * used on the user account page for the partners with 
 * activated destinations functionality
 *
 * @param int $partnerID
 * @param int $userID
 * @param int $page - page number
 * @param int $ppg - items per page
 * @param string  $filter - SQL filter inserted into queries
 * @return array
 */
function loadUserDestinationsList($partnerID, $userID, $page = 1, $ppg=20, $filter=''){
	global $db;

	$sql = "SELECT UD.*,D.*, UD.*
			FROM ".T_DESTINATIONS. " D
				INNER JOIN ".T_USERS_DESTINATIONS ." UD ON (D.destinationID = UD.destinationID  AND (UD.userID = '$userID')) 
		  		LEFT JOIN " .T_DESTINATIONS_CATS. " DC ON (D.dcID = DC.dcID)
		WHERE (partnerID = 0 OR partnerID = '$partnerID') $filter
		ORDER BY FIND_IN_SET(destinationType,'account'), destinationTitle DESC";
	$sql_cnt = "SELECT COUNT(*) cnt
			FROM ".T_DESTINATIONS. " D 
			INNER JOIN ".T_USERS_DESTINATIONS ." UD ON (D.destinationID = UD.destinationID  AND (UD.userID = '$userID')) 
		WHERE (D.partnerID = 0 OR D.partnerID = '$partnerID') $filter";

	$list = new dbCustomList($db, $sql, $sql_cnt, $page, $ppg);
	$list->loadData();
	foreach ($list->list as $k=>$v){
		$list->list[$k]['destinationData']=unserialize($v['destinationData']);
		$list->list[$k]['udData']=unserialize($v['udData']);
	}
	return $list;
}

function enableUserDestination($adminID=0, $userID, $destinationID){
	global $db;
	$db->query("UPDATE ".T_USERS_DESTINATIONS." SET adminID='$adminID', udDisabled='0', udDisabledReason='' WHERE userID='$userID' AND destinationID='$destinationID'");
}
function disableUserDestination($adminID=0, $userID, $destinationID, $disabledReason=''){
	global $db;
	//pe("UPDATE ".T_USERS_DESTINATIONS." SET adminID='$adminID', udDisabled='1' , udDisabledReason='$disabledReason', udDisabledWhen=NOW() WHERE userID='$userID' AND destinationID='$destinationID'");
	$db->query("UPDATE ".T_USERS_DESTINATIONS." SET adminID='$adminID', udDisabled='1' , udDisabledReason='$disabledReason', udDisabledWhen=NOW() WHERE userID='$userID' AND destinationID='$destinationID'");
}
/**
 * Adds system defined destination into the user's destinations list
 * saves the amount of user destination rebate
 *
 * @param int $userID
 * @param int $destinationID
 * @param float $udRebate - rebate size
 */
function setUserDestinationRebate($userID, $destinationID, $udRebate){
	global $db;
	$db->query("UPDATE " . T_USERS_DESTINATIONS . " SET udRebate = '$udRebate' WHERE destinationID ='$destinationID' AND userID='$userID'");
}

// update destination rebates for user
function recalculateUserDestinationRebates($userID){
	global $db;
	if($userID>0){
		$user = new dbObject($db, T_USERS, $userID);
		
		$disabledDestinations = loadUserDestinationsList($user->profile['partnerID'], $user->id, 1, 50, " AND UD.udDisabled=1");
		foreach($enabledDestinations->list as $v) setUserDestinationRebate($user->id, $v['destinationID'],0);
		
		$enabledDestinations = loadUserDestinationsList($user->profile['partnerID'], $user->id, 1, 50, " AND UD.udDisabled=0");
		$c = count($enabledDestinations->list);
		if($c>0){
			$defaultRebate = round((1/$c), 5);
			foreach($enabledDestinations->list as $k=>$v) setUserDestinationRebate($user->id, $v['destinationID'], $defaultRebate);
		}
	}
}



//*********************************************** SMARTY ORIENTED FUNCTIONS ***************************************************

/**
 * Generate banner url
 *
 */
function printBannerUrl($params, &$smarty){

	$result = str_replace ( "./", "", $params ['path'] );
	$result = ($result != "") ? $result : $params ['default'];

	$result = UPLOAD_URL . $result;

	return $result;
}
function printURL($params, &$smarty){
	$result = $params ['category'];
	$result .= ($params['merchant'])? '/'.$params['merchant'] : '';

	return makeURL($result);
}
/**
 * Returns label depending on the points number
 *
 * @param unknown_type $params
 * @param unknown_type $smarty
 * @return unknown
 */
function printPointsLabel($params, &$smarty){
	$amount = round ($params['value'] );
	$result = $amount . ' point' . (($amount == 1) ? '' : 's');
	return $result;
}


//************************************************* EMAIL ORIENTED FUNCTIONS***************************************************


/**
 * Extract subject from the email template
 */
function getSubject(&$body){
	preg_match ( "@Subject:[^\w]*(.*)[\r\n]@", $body, $parts );
	$body = preg_replace ( "@Subject:(.*)[\r\n]@", "", $body );
	return $parts [1];
}
/**
 * Send registration email
 */
function sendRegistrationEmail($userID, $userPassowrdDecoded, $emailFlags = EMAIL_FLAGS){
	GLOBAL $db, $smarty;

	$user = new dbObject( $db, T_USERS, $userID );
	$user->profile ['userPassword'] = $userPassowrdDecoded;
	$smarty->assign ( 'user', $user->profile );
	$body = $smarty->fetch ( 'emails/registration.email' );

	$subject = getSubject ( $body );
	@mail ( $user->profile ['userEmail'], $subject, $body, $emailFlags );
	@mail ( ADMIN_EMAIL, $subject, $body, $emailFlags );
}

//*********************************************************************************************************************
//****************************** DESTINATIONS(CHARITIES) ORIENTED FUNCTIONS *******************************************
//*********************************************************************************************************************
/**
 * Returns list of destinations categories
 *
 * @return array
 */
function getDestinationsCategories(){
	global $db;
	$sql = "SELECT * FROM " . T_DESTINATIONS_CATS;
	$db->query($sql);
	$cats = array();
	while($db->next_record()){
		$cats[$db->f('dcID' )] = $db->f('dcTitle' );
	}
	return $cats;
}
/**
 * Returns list of destinations categories with numbers of destinations for each category
 *
 */
function getDestinationsCategoriesList($partnerID = 0){
	global $db;
	$sql = "SELECT dc.dcID, dcTitle, COUNT(d.destinationID) cnt
			FROM " . T_DESTINATIONS_CATS . " dc 
			LEFT JOIN " . T_DESTINATIONS . " d ON(dc.dcID = d.dcID)" . 
			($partnerID > 0 ? " INNER JOIN " . T_PARTNERS_DESTINATIONS . " pd ON(pd.destinationID = d.destinationID AND pd.partnerID='$partnerID')" : '') .
			" GROUP BY dc.dcID HAVING(cnt > 0)";
			$db->query($sql);
			$cats = array();
			while ( $db->next_record() ){
				$cats [$db->f('dcID' )] = $db->Record;
			}
			return $cats;
}
/**
 * Returns list of destinations for current category
 *
 */
function getPartnerDestinations($categoryID = 0, $partnerID = 0){
	global $db;
	$sql = "SELECT d.*, (pd.partnerID  is not null) as checked
			FROM " . T_DESTINATIONS . " d 
			LEFT OUTER JOIN ".T_PARTNERS_DESTINATIONS." pd ON (pd.destinationID = d.destinationID " . ($partnerID > 0 ? " AND pd.partnerID='$partnerID' " : '') . ") 
			WHERE 1=1 " . ($categoryID > 0 ? " AND dcID='$categoryID' " : '') . " LIMIT 100";

	$db->query($sql);
	while ( $db->next_record() ){
		$destinatons [] = $db->Record;
	}
	return $destinatons;
}
/**
 * Check or uncheck current destination for partner
 *
 */
function setPartnerDestination($partnerID = 0, $destinationID = 0, $checked = '1'){
	global $db;
	if ($partnerID && $destinationID){
		$db->query("DELETE FROM " . T_PARTNERS_DESTINATIONS . " WHERE partnerID='$partnerID' AND destinationID = '$destinationID'" );

		if ($checked){
			$db->query("INSERT INTO " . T_PARTNERS_DESTINATIONS . " SET partnerID='$partnerID', destinationID = '$destinationID'" );
		}
	}
}
/**
 * Returns user available destionations 
 *
 */
function getUserDestinations($categoryID = 0, $partnerID = 0, $userID = 0){
	global $db;
	$sql = "SELECT d.*, (pd.partnerID  is not null) as checked
			FROM " . T_DESTINATIONS . " d 
			LEFT OUTER JOIN " . T_PARTNERS_DESTINATIONS . " pd ON (pd.destinationID = d.destinationID " . ($partnerID > 0 ? " AND pd.partnerID='$partnerID' " : '') . ") 
			WHERE 1=1 " . ($categoryID > 0 ? " AND dcID='$categoryID' " : '') . " LIMIT 1000";

	$db->query($sql);
	while ( $db->next_record() ){
		$destinatons [] = $db->Record;
	}
	return $destinatons;
}


//****************************** PARTNER ARTICLES ORIENTED FUNCTIONS **********************************************************

/**
 * Returns list of articles categories
 *
 */
function getArticlesCategores(){
	global $db;
	$sql = "SELECT * FROM " . T_ARTICLES_CATEGORIES . " ";
	$db->query($sql);
	$cats = array();
	while ( $db->next_record() ){
		$cats [$db->f('acID' )] = $db->f('acTitle' );
	}
	return $cats;
}
/**
 * Returns article category title by acID
 *
 */
function getArticleTitle($acID){
	global $db;
	if ($acID > 0)
	$sql = "SELECT acTitle FROM " . T_ARTICLES_CATEGORIES . " WHERE acID='$acID' LIMIT 1";
	$db->query($sql);
	$db->next_record();
	return $db->Record['acTitle'];
}
/**
 * Returns count of articles for current category 
 *
 */
function getCntArticles($categryID = 1){
	global $db;
	$sql = "SELECT count(articleID) cnt FROM " . T_ARTICLES . " WHERE articleCategory='" . $categryID . "'";
	$db->query($sql);
	$db->next_record();
	return $db->f('cnt' );
}
/**
 * Return list of articles for current category
 *
 */
function getArticlesList($categryID = 1){
	global $db;
	$sql = "SELECT * FROM " . T_ARTICLES . " WHERE articleCategory='" . $categryID . "'";
	$db->query($sql);
	$articles = array();
	while ( $db->next_record() ){
		$articles [] = $db->Record;
	}
	return $articles;
}
/**
 * Return news for current partner
 *
 */
function getNewsForPartner($partnerID, $not_read = 1, $limit = 10){
	global $db;
	if ($not_read){
		$sql = "SELECT * FROM " . T_NEWS . " WHERE (newsID IN (SELECT newsID FROM " . T_PARTNERS_NEWS . " WHERE partnerID ='$partnerID' AND newsRead='n'))
				ORDER BY newsAdded DESC LIMIT $limit ";
	} else {
		$sql = "SELECT *,(newsID IN (SELECT newsID FROM " . T_PARTNERS_NEWS . " WHERE partnerID ='$partnerID')) as r
				FROM " . T_NEWS . " ORDER BY r DESC, newsAdded DESC LIMIT $limit ";
	}
	$db->query($sql);
	$news = $db->getarray();

	return $news;
}
/**
 * Returns list of partners for current News
 *
 */
function getPartnersForNews($newsID){
	global $db;

	$sql = "SELECT partnerID, (newsRead='y') as r FROM " . T_PARTNERS_NEWS . " WHERE newsID ='$newsID'";
	$db->query($sql);
	$partners = array();
	while ( $db->next_record() ){
		$partners [$db->f('partnerID' )] = $db->f('r' );
	}
	return $partners;
}

/**
 * Set current news for partners list
 *
 */
function setNewsForPartners($partnersIDs = array(), $newsID){
	global $db;

	$db->query("DELETE FROM " . T_PARTNERS_NEWS . " WHERE newsID='$newsID'" );

	foreach ( $partnersIDs as $partnerID ){ // add news for chosen partners
		$sql = "REPLACE INTO " . T_PARTNERS_NEWS . " SET partnerID='$partnerID', newsID='$newsID'";
		$db->query($sql);
	}
}
/**
 * Set news as Read for current partner
 * 
 */
function setNewsForPartnerAsRead($partnerID){
	global $db;
	$sql = "UPDATE " . T_PARTNERS_NEWS . " SET newsRead='y', newsDateRead=NOW() WHERE partnerID='$partnerID'";
	$db->query($sql);
}



//************************************************* STATISTICS FUNCTIONS ******************************************************


//************************************************* CONTENT STATS FOR PARTNER *************************************************
/**
 * Returns purchases statistic by merchants
 *
 */
function loadPurchasesStatsByMerchants($partnerID, $date = '0000-00-00', $orderBy = 'sum'){
	global $db;
	switch ( $orderBy){
		case 'cnt' :
			$select = " M.merchantTitle Title, COUNT(P.purchaseAmount) value ";
			$sort = "value DESC ";
			break;
		case 'sum' :
			$select = "M.merchantTitle Title, SUM(P.purchaseAmount/P.purchaseExchangeRate) value";
			$sort = "value DESC";
			break;
		case 'name' :
		default :
			$select = "M.merchantTitle Title, COUNT(P.purchaseAmount) value ";
			$sort = "Title";
			break;
	}
	$sql = "SELECT " . $select . "
			FROM " . T_PURCHASES . " P
				LEFT JOIN " . T_MERCHANTS . " M ON (M.merchantID = P.merchantID )
				INNER JOIN " . T_PSTATUSES . " PS ON (PS.statusID = P.statusID)
			WHERE PS.statusTitle='Approved' AND P.partnerID='{$partnerID}' AND YEAR(purchaseDate)=YEAR('$date') AND MONTH(purchaseDate)=MONTH('$date')
			GROUP BY Title ORDER BY " . $sort;

	$items = new dbCustomList($db, $sql);
	$items->loadData();
	return $items->list;
}
/**
 * Returns purchases statistic by categories
 *
 */
function loadPurchasesStatsByCategory($partnerID, $date = '0000-00-00', $orderBy = 'sum'){
	global $db;
	switch ( $orderBy){
		case 'cnt' :
			$select = " C.categoryTitle Title, COUNT(P.purchaseID) value ";
			$sort = "value DESC ";
			break;
		default :
		case 'sum' :
			$select = "C.categoryTitle Title, SUM(P.purchaseAmount/P.purchaseExchangeRate) value";
			$sort = "value DESC";
			break;
	}
	$sql = "SELECT " . $select . " FROM " . T_PURCHASES . " P
			LEFT JOIN ".T_MERCHANTS." M ON (M.merchantID = P.merchantID )
			LEFT JOIN ".T_CATS." C ON (C.categoryID = M.categoryID)
			INNER JOIN ".T_PSTATUSES." PS ON (PS.statusID = P.statusID)
			WHERE PS.statusTitle='Approved' AND P.partnerID='{$partnerID}' AND YEAR(purchaseDate)=YEAR('$date') AND MONTH(purchaseDate)=MONTH('$date')
			GROUP BY Title 
			ORDER BY " . $sort;

	$items = new dbCustomList($db, $sql);
	$items->loadData();
	return $items->list;
}

//**************************** TIME STATS FOR PARTNER ****************************************************
/**
 * Returns seconds as "X days XX h XXm XXs"
 *
 * @param int $seconds
 * @return string
 */
function sec2str($seconds){
	$n = round ( $seconds );
	$sec = $n % 60;
	$n -= $sec;

	$min = ($n % (60 * 60)) / 60;
	$n -= $min * 60;

	$hrs = ($n / (60 * 60)) % 24;
	$n -= $hrs * 60 * 60;
	$hrs = $hrs ? $hrs . 'h ' : '';

	$days = ($n / (60 * 60 * 24));
	$days = $days ? $days . 'days ' : '';
	return /*$days . $hrs .*/ $min . 'm ' . $sec . 's';
}
/**
 * Returns dates for statistic by time
 *
 */
function loadTimeStatsDates($partnerID, $statusID = ''){
	global $db;

	$sql = "SELECT YEAR(l.logAdded) as year
			FROM " . T_LOGS . " l 
			WHERE l.logPersonType='u' AND l.partnerID='$partnerID' 
			GROUP BY year HAVING (year > 2000) ORDER BY year";
	$db->query($sql);

	$rows = $db->getArray();
	$dates = array();

	foreach ($rows as $k => $v){
		$sql = "SELECT LPAD(MONTH(l.logAdded),2,'0') as m,
						MONTHNAME(l.logAdded) as month,
						YEAR(l.logAdded) as year
				FROM " . T_LOGS . " l 
				WHERE l.logPersonType='u' AND l.partnerID='$partnerID' AND YEAR(l.logAdded)='{$v['year']}' 
				GROUP BY month ORDER BY year DESC, m DESC";
		$db->query($sql);
		while($db->next_record()){
			$dates[$v['year']][$db->f("m")] = $db->f("month");
		}
	}
	
	return $dates;
}
/**
 * Returns statistic by time between first user login and first user purchase
 *
 */
function loadTimeStatsByLoginPurchase($partnerID, $date = '0000-00-00'){
	global $db;
	$result = array();
	$sql = "SELECT ABS(AVG(t)) a FROM (
						SELECT UNIX_TIMESTAMP(min(p.purchaseDate))-UNIX_TIMESTAMP(min(l.logAdded)) t
						FROM " . T_USERS . " u 
						INNER JOIN " . T_LOGS . " l ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.logType='login')
						INNER JOIN " . T_PURCHASES . " p ON (u.userID=p.userID AND  u.partnerID=p.partnerID AND p.partnerID='$partnerID')
						WHERE YEAR('$date')=YEAR(p.purchaseDate) GROUP BY u.userID) n";
	$db->query($sql);
	$db->next_record();
	$result['year'] = $db->f('a');

	$sql = "SELECT ABS(AVG(t)) a FROM (
						SELECT UNIX_TIMESTAMP(min(p.purchaseDate))-UNIX_TIMESTAMP(min(l.logAdded)) t
						FROM " . T_USERS . " u 
						INNER JOIN " . T_LOGS . " l ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.logType='login')
						INNER JOIN " . T_PURCHASES . " p ON (u.userID=p.userID AND  u.partnerID=p.partnerID AND p.partnerID='$partnerID')
						WHERE YEAR('$date')=YEAR(p.purchaseDate) AND MONTH('$date')=MONTH(p.purchaseDate) GROUP BY u.userID ) n";
	$db->query($sql);
	$db->next_record();
	$result ['month'] = $db->f('a' );

	$sql = "SELECT ABS(AVG(t)) a FROM (
						SELECT UNIX_TIMESTAMP(min(p.purchaseDate))-UNIX_TIMESTAMP(min(l.logAdded)) t
						FROM " . T_USERS . " u 
						INNER JOIN " . T_LOGS . " l ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.logType='login')
						INNER JOIN " . T_PURCHASES . " p ON (u.userID=p.userID AND  u.partnerID=p.partnerID AND p.partnerID='$partnerID')
						WHERE YEAR('$date')=YEAR(p.purchaseDate) AND WEEK('$date')=WEEK(p.purchaseDate) GROUP BY u.userID) n";
	$db->query($sql);
	$db->next_record();
	$result['week'] = $db->f('a');
	return $result;
}

/**
 * Returns average time between first users login and first user purchases
 *
 */
function loadTimeStatsLoginPurchaseYear($partnerID, $year = ''){
	global $db;
	if (!is_numeric($year)){
		$year = date("Y");
	}
	$result = array();
	$db->query("SELECT mm, AVG(t) a FROM (
					SELECT UNIX_TIMESTAMP(min(p.purchaseDate))-UNIX_TIMESTAMP(min(l.logAdded)) t, MONTH(p.purchaseDate) mm
					FROM " . T_USERS . " u 
					INNER JOIN " . T_LOGS . " l ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.logType='login')
					INNER JOIN " . T_PURCHASES . " p ON (u.userID=p.userID AND  u.partnerID=p.partnerID AND p.partnerID='$partnerID')
					WHERE MONTH(p.purchaseDate)=MONTH(l.logAdded) AND UNIX_TIMESTAMP(p.purchaseDate)>UNIX_TIMESTAMP(l.logAdded) AND YEAR(p.purchaseDate)='$year' GROUP BY u.userID, mm
					) 
				n GROUP BY mm" );
	while($db->next_record())
	$result[$db->f('mm')] = round($db->f('a'));

	return $result;
}
/**
 * Returns average time between users visits
 *
 */
function loadTimeStatsBetweenVisits($partnerID, $date = '0000-00-00'){
	global $db;

	$partner = new dbObject( $db, T_PARTNERS, $partnerID );
	$gmt = is_numeric ( $partner->profile ['partnerGMT'] ) ? $partner->profile ['partnerGMT'] : 0;

	$result = array();
	$db->query("SELECT AVG(t)/cnt a FROM
				(SELECT UNIX_TIMESTAMP(MAX( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ))-UNIX_TIMESTAMP(MIN( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) )) t, COUNT(l.logAdded) cnt 
				FROM " . T_LOGS . " l
				INNER JOIN " . T_USERS . " u ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.partnerID='$partnerID')
				WHERE YEAR('$date')=YEAR(ADDDATE(l.logAdded, INTERVAL $gmt HOUR))  AND l.logType='login' GROUP BY l.logPersonID) n" );
	$db->next_record();
	$result ['year'] = $db->f('a' );

	$db->query("SELECT AVG(t)/cnt a FROM
				(SELECT UNIX_TIMESTAMP(MAX( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ))-UNIX_TIMESTAMP(MIN( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) )) t, COUNT(l.logAdded) cnt 
				FROM " . T_LOGS . " l
				INNER JOIN " . T_USERS . " u ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.partnerID='$partnerID')
				WHERE MONTH('$date')=MONTH(ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ) AND l.logType='login' GROUP BY l.logPersonID) n" );
	$db->next_record();
	$result ['month'] = $db->f('a' );

	$db->query("SELECT AVG(t)/cnt a FROM
				(SELECT UNIX_TIMESTAMP(MAX( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ))-UNIX_TIMESTAMP(MIN( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) )) t, COUNT(l.logAdded) cnt 
				FROM " . T_LOGS . " l
				INNER JOIN " . T_USERS . " u ON (u.userID=l.logPersonID AND l.logPersonType='u' AND l.partnerID='$partnerID')
				WHERE WEEK('$date')=WEEK(ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ) AND l.logType='login' GROUP BY l.logPersonID) n" );
	$db->next_record();
	$result ['week'] = $db->f('a' );
	return $result;
}
/**
 * Returns average time between users visits by months
 *
 */
function loadTimeStatsBetweenVisitsYear($partnerID, $year = ''){
	global $db;

	$partner = new dbObject( $db, T_PARTNERS, $partnerID);
	$gmt = is_numeric($partner->profile['partnerGMT'])?$partner->profile['partnerGMT'] : 0;

	if (! is_numeric($year )){
		$year = date("Y");
	}
	$result = array();
	$db->query("SELECT mm , AVG(t)/cnt a FROM
				(SELECT UNIX_TIMESTAMP(MAX(ADDDATE(l.logAdded, INTERVAL $gmt HOUR)))-UNIX_TIMESTAMP(MIN(ADDDATE(l.logAdded, INTERVAL $gmt HOUR) )) t, COUNT(l.logAdded) cnt, MONTH(ADDDATE(l.logAdded, INTERVAL $gmt HOUR)) mm
				    FROM " . T_LOGS . " l 
				    INNER JOIN " . T_USERS . " u ON (u.userID=l.logPersonID AND l.partnerID='$partnerID')
				    WHERE '$year'=YEAR(ADDDATE(l.logAdded, INTERVAL $gmt HOUR)) AND l.logType='login' GROUP BY l.logPersonID, mm) n GROUP BY mm" );
	while ( $db->next_record() )
	$result [$db->f('mm' )] = round ( $db->f('a' ) );
	return $result;
}
/**
 * Returns times statistic by busiest time of day
 *
 */
function loadTimeStatsBusiestTimeOfDay($partnerID, $date = '0000-00-00'){
	global $db;

	$partner = new dbObject( $db, T_PARTNERS, $partnerID );
	$gmt = is_numeric ( $partner->profile ['partnerGMT'] ) ? $partner->profile ['partnerGMT'] : 0;

	$result = array();
	$db->query("SELECT dt, dweek, dname, MAX(cnt) cnt, h FROM (
					SELECT CONCAT(MONTH( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ),'/',DAY( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ),'/',YEAR(ADDDATE(l.logAdded, INTERVAL $gmt HOUR) )) dt, DAYOFWEEK(ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ) dweek, DAYNAME( ADDDATE(l.logAdded, INTERVAL $gmt HOUR)) dname,HOUR( ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ) h, COUNT(l.logPersonID) cnt
					FROM " . T_LOGS . " l
					INNER JOIN " . T_USERS . " u ON (u.userID=l.logPersonID AND l.logPersonType='u' AND u.partnerID='$partnerID')
					WHERE YEAR('$date')=YEAR(ADDDATE(l.logAdded, INTERVAL $gmt HOUR) ) AND WEEK('$date')=WEEK(ADDDATE(l.logAdded, INTERVAL $gmt HOUR)) AND l.logType='login' GROUP BY h)  
				n GROUP BY dweek" );

	while ( $db->next_record() )
	$result [$db->f('dweek' )] = array('date' => $db->f('dt' ), 'cnt' => $db->f('cnt' ), 'time' => $db->f('h' ) . ':00' );
	return $result;
}

/**
 * Returns times statistic by busiest day of week
 *
 */
function loadTimeStatsBusiestDayofWeek($partnerID, $date = '0000-00-00'){
	global $db;
	$result = array();
	$db->query("SELECT count(clickID) cnt, DAYNAME(clickDate) d
				FROM " . T_CLICKS . " 
				WHERE partnerID='$partnerID' AND 
				YEAR(clickDate)=YEAR('$date') GROUP BY d ORDER BY cnt DESC LIMIT 1" );
	$db->next_record();
	$result ['year'] = $db->f('d' );

	$db->query("SELECT count(clickID) cnt, DAYNAME(clickDate) d
				FROM " . T_CLICKS . "  
				WHERE partnerID='$partnerID' AND 
				YEAR('$date')=YEAR(clickDate) AND MONTH(clickDate)=MONTH('$date') GROUP BY d ORDER BY cnt DESC LIMIT 1" );

	$db->next_record();
	$result ['month'] = $db->f('d' );

	$db->query("SELECT count(clickID) cnt, DAYNAME(clickDate) d
				FROM " . T_CLICKS . " 
				WHERE partnerID='$partnerID' AND 
				YEAR('$date')=YEAR(clickDate) AND WEEK(clickDate)=WEEK('$date') GROUP BY d ORDER BY cnt DESC LIMIT 1" );
	$db->next_record();
	$result ['week'] = $db->f('d' );

	return $result;
}
/**
 * Returns busiest month of year
 *
 */
function loadTimeStatsBusiestMonthsofYear($partnerID){
	global $db;
	$result = array();
	$db->query("SELECT MAX(cnt) c, m, y FROM(
					SELECT count(clickID) cnt, MONTHNAME(clickDate) m, YEAR(clickDate) y  
					FROM " . T_CLICKS . " WHERE partnerID='$partnerID' 
					 GROUP BY m) f GROUP BY y ORDER BY y" );
	while ( $db->next_record() ){
		$result [$db->f('y' )] = $db->f('m' );
	}
	return $result;
}
//******************************* USERS STATS FOR PARTNER ****************************************************
/**
 * Returns actual moths of years for calendar tabs
 *
 */
function loadUserStatsMonthsofYear($partnerID){
	global $db;

	$sql = "SELECT YEAR(userAdded) as year FROM " . T_USERS . " WHERE partnerID='$partnerID' GROUP BY year";
	$db->query($sql);
	$rows = $db->getarray();
	$dates = array();

	foreach ( $rows as $k => $v ){
		$sql = "SELECT LPAD(MONTH(userAdded),2,'0') as m, MONTHNAME(userAdded) as month,YEAR(userAdded) as year  FROM " . T_USERS . " WHERE partnerID='$partnerID' GROUP BY year, month HAVING year='{$v['year']}' ORDER BY year DESC,m ASC";
		$db->query($sql);
		while ( $db->next_record() ){
			$dates [$v ['year']] [$db->f("m" )] = $db->f("month" );
		}
	}
	return $dates;
}
/**
 * Returns statistic of users registrations
 *
 */
function loadUsersStats($partnerID, $date = '0000-00-00', $gender = 'all'){
	global $db;

	$genderFilter = ($gender == 'm' || $gender == 'f')? " AND userGender = '$gender' " : '';

	$result = array();
	$db->query("SELECT count(userID) cnt FROM " . T_USERS . " WHERE partnerID='$partnerID' AND userEnabled='y' $genderFilter
				AND YEAR(userAdded)=YEAR('$date')" );
	$db->next_record();
	$result ['year'] = $db->f('cnt' );

	$db->query("SELECT count(userID) cnt FROM " . T_USERS . " WHERE partnerID='$partnerID' AND userEnabled='y' $genderFilter
				AND YEAR(userAdded)=YEAR('$date') AND MONTH(userAdded) = MONTH('$date')" );
	$db->next_record();
	$result ['month'] = $db->f('cnt' );

	$db->query("SELECT count(userID) cnt FROM " . T_USERS . " WHERE partnerID='$partnerID' AND userEnabled='y' $genderFilter
				AND YEAR(userAdded)=YEAR('$date') AND WEEK(userAdded) = WEEK('$date')" );
	$db->next_record();
	$result ['week'] = $db->f('cnt' );

	return $result;
}
/**
 * Returns statistic of users ages
 *
 */
function loadUsersStatsAge($partnerID, $filterAge = "age >= 18 AND age <= 100", $gender = 'all'){
	global $db;
	if ($gender == 'm' || $gender == 'f')
	$filterGender = " AND userGender = '$gender' "; else
	$filterGender = '';
	$db->query("SELECT COUNT(age) cnt FROM (
					SELECT (YEAR(NOW())-userBirth) as age 
					FROM " . T_USERS . " WHERE partnerID='$partnerID' AND userEnabled = 'y' $filterGender AND userBirth HAVING($filterAge)) m;" );
	$db->next_record();
	$result ['cnt'] = $db->f('cnt' );

	return $result;
}


//*********************************************** COMMENTS ORIENTED FUNCTIONS *************************************************


/**
 * Returns list of user commentaries for current merchant
 *
 */
function getComments($partnerID, $merchantID){
	global $db;
	$gmt = is_numeric ( $partner->profile ['partnerGMT'] ) ? $partner->profile ['partnerGMT'] : 0;

	$sql = " SELECT commentID, partnerID, merchantID, merchantRating, commentUserName,
				commentBody, ADDDATE(commentDate, INTERVAL $gmt HOUR) as commentDate, commentPublished, commentUserIp
				 FROM " . T_COMMENTS . " WHERE  partnerID=$partnerID AND merchantID='$merchantID' AND commentPublished='1' ORDER BY commentDate DESC";
	$db->query($sql);
	$comments = array();
	while ($db->next_record()){
		$comments [] = $db->Record;
	}
	return $comments;
}
/**
 * Add comment and rating for current merchant
 *
 * @param int $partnerID
 * @param int $merchantID
 * @param string $userName
 * @param int $rating
 * @param string $comment
 * @return 1 if comment added, 0 if user IP is banned
 */
function addComments($partnerID, $merchantID, $name, $rating, $comment,$userProfile=array(),$userIp=0){
	global $db;
	$site = new dbObject( $db, T_PARTNERS, $partnerID ); // $site->profile['partnerCommentsApprove']
	// 0 - display reviews immediately, 1 - approved by admin, 2 - approved by partner
	$ip = $userIp?$userIp:$_SERVER ['REMOTE_ADDR'];

	$banned_ips = getBanIPs( $partnerID );
	foreach ( $banned_ips as $item ){
		$ips [] = $item ['bansIp'];
	}

	if (! in_array( $ip, $ips )){
		$GMTnow = getGmtDBDate ();
		$sql = "INSERT INTO " . T_COMMENTS . "
				SET partnerID='$partnerID',userID='{$userProfile['userID']}',merchantID='$merchantID',merchantRating='$rating',commentUserName='$name',commentBody='$comment',commentDate='$GMTnow', commentUserIp='$ip'" . ($site->profile ['partnerCommentsApprove'] == 0 ? " , commentPublished='1' " : "");
		$db->query($sql);
		return 1;
	}
	return 0;
}
/**
 * Set commentPublished as true fo� current comment
 *
 * @param int $partnerID
 * @param int $commentID
 */
function approveComment($partnerID, $commentID){
	global $db;
	$sql = "UPDATE " . T_COMMENTS . " SET commentPublished='1' WHERE commentID='$commentID' AND partnerID='$partnerID'";
	$db->query($sql);
	logDB($partnerID,$partnerID,'p','partner approved comment', 'Comment ID: '.$commentID, serialize($_SESSION));
}
/**
 * Remove current comment
 *
 * @param int $partnerID
 * @param int $commentID
 */
function deleteComment($partnerID, $commentID){
	global $db;
	$sql = "DELETE FROM " . T_COMMENTS . " WHERE commentID='$commentID' AND partnerID='$partnerID'";
	$db->query($sql);
	logDB($partnerID,$partnerID,'p','partner delete comment', 'Comment ID: '.$commentID, serialize($_SESSION));
}
/**
 * Edit content of current comment
 *
 * @param int $partnerID
 * @param int $commentID
 * @param string new text $commentBody
 */
function editComment($partnerID, $commentID, $commentBody = ''){
	global $db;
	$sql = "UPDATE " . T_COMMENTS . " SET commentBody='$commentBody' WHERE commentID='$commentID' AND partnerID='$partnerID'";
	$db->query($sql);
}
/**
 * Add current ip-address in list of banned IPs
 *
 */
function banIP($partnerID, $ip, $data = ''){
	global $db;
	$sql = "INSERT INTO " . T_BANS . " SET partnerID='$partnerID', bansIp='$ip', bansDate=NOW(), bansData='$data'";
	$db->query($sql);
	logDB($partnerID,$partnerID,'p','partner banned ip', 'IP: '.$ip, serialize($_SESSION));
}
/**
 * Returns list of banned IPs
 *
 */
function getBanIPs($partnerID = 0){
	global $db;
	$ips = new dbList ( $db, T_BANS, ($partnerID ? " AND partnerID='" . $partnerID . "'" : ''), 1, 99999 );
	$ips->loadData();
	return $ips->list;
}

//******************************** RATINGS(comment) ORIENTED FUNCTIONS **********************************************************
/**
 * Returns merchant rating for current partner
 *
 */
function getMerchantRating($partnerID, $merchantID){
	global $db;
	$sql = "SELECT merchantRating FROM " . T_PARTNERS_MERCHANTS . " WHERE partnerID = '$partnerID' AND merchantID='$merchantID'";
	$db->query($sql);
	while ( $db->next_record() )
	$rating = $db->f('merchantRating' );
	return $rating;
}
/**
 * Recalculate rating for current merchants
 *
 */
function updateRating($partnerID, $merchantID){
	global $db;
	$sql = "SELECT COUNT(merchantRating) cnt, SUM(merchantRating) summ  FROM " . T_COMMENTS . " WHERE partnerID = $partnerID AND merchantID='$merchantID'  AND commentPublished = '1'";
	$db->query($sql);
	while ( $db->next_record() ){
		$cnt = $db->f('cnt' );
		$summ = $db->f('summ' );
	}
	$rating = ($cnt == 0) ? 0 : $summ / $cnt;
	$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantRating='$rating' WHERE partnerID = $partnerID AND merchantID= $merchantID";
	$db->query($sql);
}
/**
 * Recalculate clicks for current merchants
 *
 */
function updateMonthClicks($partnerID, $merchantID){
	global $db;
	$sql = "select count(clickID) cnt, merchantID from " . T_CLICKS . "
			WHERE (clickDate> SUBDATE(NOW(), INTERVAL 1 MONTH)) AND (partnerID=$partnerID) AND (merchantID=$merchantID) 
			GROUP BY merchantID LIMIT 1";
	$db->query($sql);
	$db->next_record();
	$cnt = $db->f('cnt');

	$sql = "UPDATE " . T_PARTNERS_MERCHANTS . " SET merchantClicks='$cnt' WHERE partnerID = $partnerID AND merchantID = $merchantID";
	$db->query($sql);
}
/**
 * Recalculate rating for all merchants and all partners
 *
 */
function updateMerchantsRating(){
/*	$merchants = getMerchants();
	$partners = getPartners();
	foreach ($merchants as $km => $vm){
		foreach ($partners as $kp=>$vp){
			updateRating($kp, $km);
			updateMonthClicks($kp, $km);
		}
	}
*/
}
/**
 * Recalculate rating for all merchants and current partner
 *
 */
function updatePartnerMerchantsRating($partnerID){
	$merchants = getMerchants();
	foreach ( $merchants as $km=>$vm){
		updateRating ( $partnerID, $km);
		updateMonthClicks ( $partnerID, $km);
	}
}





//************************************ CACHE ORIENTED FUNCTIONS ***

/**
 * Saves cached data into a temporary file
 *
 * @param string $cacheID - unique cache item id
 * @param string $cacheContent - cached information
 * @param int $ttl - TTL number for the cached information
 */
function setCache($cacheID='',$cacheContent=''){
	$cache = CACHE_DIR.base64_encode($cacheID).'.cache';
	file_put_contents($cache,$cacheContent);
}

/**
 * Loads information from cache file
 *
 * @param string $cacheID - cache item identifier
 * @return string
 */
function getCache($cacheID='',$ttl=CACHE_TTL){
	$cache = CACHE_DIR.base64_encode($cacheID).'.cache';
	$cacheCreated = @filemtime($cache);
	if(($cacheCreated+$ttl) > time()){
		return file_get_contents($cache);
	}else{
		return false;
	}
}

function getPurchaseIDByTransactionID($networkID,$addedPurchsaseTransactionID)
{
            global $db;
            if(empty($addedPurchsaseTransactionID)) return 0;
            $sql = "SELECT purchaseID FROM " . T_PURCHASES . " p inner join ".T_MERCHANTS ." m on p.merchantid=m.merchantid
                        WHERE m.networkID = '$networkID' AND purchaseTransactionID='$addedPurchsaseTransactionID' LIMIT 1";
            $db->query($sql);
            return ($db->next_record())?$db->f('purchaseID'):0;
}

function getMerchantVouchersPartner($itmPartnerid)
{
	global $db;
	
	$db->query("SELECT v.*,(v.voucherActualFrom > NOW() AND v.voucherActualTo < NOW()) as actual FROM ".T_VOUCHERS." v WHERE (partnerID='$itmPartnerid')");
	return $db->getArray();
}

function loadMonthlyReport()
{
	
}

function loadVoucherDetailsList($partnerID, $categoryID, $page = 1, $ppg = 100)
{
	//$categories = loadCategoriesList($categoryID);
	global $db;
	$sql = "select w.voucherID,voucherText,m.merchantTitle,w.voucherWeight,v.partnerID from ".T_VOUCHERS." v inner join ".T_VOUCHERS_WEIGHTS." w on v.voucherid= w.voucherid 
			inner join ".T_MERCHANTS ." m on v.merchantid=m.merchantid
			where w.partnerID=$partnerID";
	$db->query($sql);
	return $db->getArray();
} 
/**
 * Save weights of merchants for current partner (the weight influences on order of display)
 *
 * @param array $weights
 */
function saveVouchersWeights($partnerID, $weights=array()){
	global $db;
	foreach($weights as $voucherID => $weight){
		logDB($partnerID, '','p','partner changed merchant weight', $voucherID.':'.$weight);
		$sql = "UPDATE " . T_VOUCHERS_WEIGHTS . " SET voucherWeight='$weight' WHERE partnerID='{$partnerID}' AND voucherID='{$voucherID}' ";		
		$db->query($sql);
	}
}
function getMerchantNetworkID($merchantNetworkID,$networkID,$partnerID)
{
	global $db;
	$sql = "select merchantID,merchantTitle from ". T_MERCHANTS."  where NetworkMerchantID = '".$merchantNetworkID."' and networkID = '".$networkID."' and partnerid = '".$partnerID."' ";
	$db->query($sql);
	return $db->getArray();
}
?>